Combine 3 worksheets to one, have only one common column.........

[itservicesmalta]

Hi all,

If possible I would like to find a method of combining 3 different worksheets to one, all 3 sheets have one common column, however column fields are not in the same order in these different sheets. And there is also the possiblity that if there are 100 rows for this common column in sheet 1, there will be less in sheet 2.

Eg: Assuming that the common column is column A. Sheet 1 cell A1, is not the same as Sheet 2 cell A2 nor in sheet 3. For example sheet 1 cell A1, is equivalent to cell A4 in sheet 2.

My output file must have all the data from all sheeets combined, based on this common column.

 

[Nutster]

You can create a new worksheet, then reference =Sheet1!A1 in the first cell of the new worksheet. In other columns, either reference subsequent cells in Sheet1 or use VLOOKUP to find the appropriate data in the other sheets. This method assumes that Sheet1 contains a record for every item you want to merge.

=VLOOKUP(A1, Sheet2!$A:$M, 7, False)

This will look in the data in Sheet2 (assuming you are using the first 13 columns om Sheet2), where the first column (A) of a row matches the value in this new sheet's A1 cell. It then returns the value in the seventh column for that row. The False at the end says the list on Sheet2 does not need to be sorted.

If the three sheets each contain data that might not appear on all the other sheets, then I think you will need to write a macro to merge the sheets according to the key field in column A.

 

[itservicesmalta]

Thanks for your reply Nutster, unfortunately, as you had rightly said, the three sheets each contain data that might not appear on all the sheets.

By any chance is there any software that can do this for me, instead of doing a macro, as I have no clue how to do it.


By any chance is there any software that can assist me in this

You can create a new worksheet, then reference =Sheet1!A1 in the first cell of the new worksheet. In other columns, either reference subsequent cells in Sheet1 or use VLOOKUP to find the appropriate data in the other sheets. This method assumes that Sheet1 contains a record for every item you want to merge.

=VLOOKUP(A1, Sheet2!$A:$M, 7, False)

This will look in the data in Sheet2 (assuming you are using the first 13 columns om Sheet2), where the first column (A) of a row matches the value in this new sheet's A1 cell. It then returns the value in the seventh column for that row. The False at the end says the list on Sheet2 does not need to be sorted.

If the three sheets each contain data that might not appear on all the other sheets, then I think you will need to write a macro to merge the sheets according to the key field in column A.