0

I have sets of data to combine in Excel 2010. Each data set has the name of the member e.g. "Alan Andrews" in row 2 column 1 and results for Alan Andrews in two further columns. The data set is in alpha order of member name and there are about 400 rows.

However the membership list and total number of members changed several times during the year that the data sets were captured. Thus in one data set Jon Wall may be row 370 and in another he may be row 372.

There are 20 data sets

I wish to create a single list in which all the names will appear in column 1 in alpha order and there will be up to 40 columns of data against each name.

I am not into VBasic. Any suggestions please?

0

Add a new sheet, and copy the member names from each dataset to column A of the new sheet, just appending to the end of the previous list. Once they're all there, Sort them and remove duplicates.

Then, use VLookup formulas to get the results for each member from the appropriate dataset.

For example, if the first dataset is on a worksheet named "dataset1", and the first result is in column F, and the second result is in column Q, then enter this formula on row 2 of the new worksheet (I assume Row 1 has a header):

=VLOOKUP($A2,dataset1!$A$2:$E$400,COLUMN(dataset1!$F$1),FALSE)

The formula to get the second result from dataset1 (column Q) is this:

=VLOOKUP($A2,dataset1!$A$2:$E$400,COLUMN(dataset1!$Q$1),FALSE)

You can copy the cell containing the formula, then paste it to the rows for all the names in your list.

You would need to do the same thing for all of the 20 datasets. If the column numbers of the results is the name, you can copy/paste the formulas across row 2 and just update the sheet name for each.

Where a particular name does not appear on a particular sheet, you will get an error (#N/A). You can either leave them, manually delete them or alter your formulas like this:

=IFERROR(VLOOKUP($A2,dataset1!$A$2:$E$400,COLUMN(dataset1!$Q$1),FALSE),"")

I hope this helps!

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.