Excel - merging, matching or detecting identical data

[Bus429]

I have been tearing out what is left of my hair trying to sort data in a spreadsheet. The long and short of it is I have a sheet of data related to tasks for a maintenance programme, I have another sheet containing maintenance manual references related to the first, one column in each sheet holding identical data (task references) but the first sheet has a few extra items not duplicated in the second. Obviously, I want to match the maintenance manual references with the tasks on one sheet.
I've tried purportedly "free" merge or join software but full functionality has to be paid for (I'm cheap). Apparently Excel 2007 has something called Lookup or similar but I'm jiggered if I can sort it out.
Has anyone a reasonably simple way of explaining how I go about joining these data?

[E.Z. Flyer]

One factor is if the worksheets are xls with formulas used to determine the values for each item/cells criterion.

One way to merge data sets is to create a new worksheet and then assign the data sets from all other worksheets to populate the new worksheet. That works if the data is static, or that it doesn't matter where it appears only that it appears within its respective column space.

It seems as if you are migrating data over to a new system and the old software is not able to export in a manner that can be imported until a new worksheet is made compatible.

However, and in all likelihood, there are formulas in place that were used to determine the values of each cell within the respective worksheets. (If you highlight a header and a formula appears in the formula bar, or that, green hash marks are seen in the worksheet) then the formulas used to equate the value of its respective cell will never merge as first written because the new worksheet is not using the same spaces as the worksheet the data sets are merged from when copying data set A1 to perhaps new data set C1 of the new worksheet.

If the new worksheet is instead a template for the new program, then of course that would be a different matter. Then, you would only need to copy the data sets without the formulas. This could be accomplished by saving the worksheets as a csv or a txt file before exporting those data sets. Then, the data sets would have to be carefully copied and parsed according to the type of data so as not to import data the new program is already programmed to determine.

[sprthompson]

Don't buy any ad-ons for Excel - it can do all sorts of amazing things...
I think all version of excel have 'vlookup'
=vlookup(cell to look up,array to look in, column from array to return, false)

If the 2 tables match e.g. each are 100 rows long with unique value in a column, then all you need to do is sort the tables by the columns and then the value will line up... (ok sounds obvious but so many people miss this)

If they are NOT the same, you can use 'countif' to find which rows from table A are in table B, and vice-versa, then you will be able to remove any excess until they are both matching, then you can use the 'sort by same column' approach above...

It's worth trying it this way because computers sort a lot more quickly than they vlookup, and the vlookup only returns one cell at a time..

Hope this made sense?

I use excel way to much at work... one of the reasons I'd rather get a CPL!

[Jofm5]

It is worth remembering with vlookup that the data has to be sorted for it to work correctly.

[Bus429]

Thanks all, for info offered.

[Bus429]

A friend talked me through Vlookup and it worked. I must say that any online sites that offer help, whether OEM or not, are so complicated and riddled with jargon - it must be the nature of the beast.

[Sprogget]

Vlookup is a great function. We use it every day populate delivery notes. We have a delivery note template where the individual fields are all vlookup references to the main job data sheet.

Prior to sussing out vlookup, we hd to write the info out each time which was a time consuming duplication of effort. I like vlookup.