EXCEL HELP!!!!!

[Excitedannie]

Please can anyone help me before I go nuts on MS Excel.

So, I have two spreadsheets. Both with names (first and surname) - different size spreadsheets because one is a subset of the other. However, both sheets have bits of information on that I need consolidated into one. For example:

Spreadsheet 1:
Miss Fanny Adams, age 100, tall
Mr Joe Bloggs, age 99, short

Spreadsheet 2:
Miss Fanny Adams, blonde, medium
Mrs Florence Nightingale, brown, small
Mr Gordon Bennett, red, large
Mr Joe Bloggs, grey, large

Desired outcome:
Miss Fanny Adams, age 100, tall blonde, medium
Mr Joe Bloggs, age 99, short, grey, large

Not sure if I've explained that properly or if that makes sense but I'd love to know if it can be done before I sit down with 1200 lines on one spreadsheet, and 2500 on the other.....

you can use vlookups to look up the info from one and add it to the other and consolidate it that way. Becomes slightly more complicated if you have people with the same names but it’s not insurmountable.

Ok thanks - v lookup isn't something I'm familiar with at all but I'll go and have a google

You can cut and paste from one to the other, whether there is a good way to select the cut area and position the paste to get exactly what you want I can't tell, you may still have to do a bit/lot of internal cutting and pasting.

Years ago I would have written a little program to read both files in and write to another file in the format I wanted, but I don't have it at my fingertips these days and it would take me forever now.

1. Sort both sheets by alphabetical order the same field (suggest using a unique identifier like ID code etc but you could use name)
2. Then copy and paste the information from the smaller data set into the larger one

In the future learn how to use Power BI as you can, with the click of a button, add both sheets to the same data model and create a single dashboard.

Vlookup ASD said but you will need to concatenate first and surname to give you a unique value to match the two data sets together on (either concatenate within the vlookup or do it first)

Use a vlookup (or better still an xlookup) to add the age and height columns on your main sheet that contains build and hair colour.

This is how I would do it

You need a field of unique info to do a data match

So title & first name & surname hopefully works be enough.

If you have two Miss Fanny Adams you will need to check manually after you've done the vlookup

Thanks all. Both sheets have surname on them so I think v lookup is the way to go. I'm reading up on it now!

You could try this once you’ve got one page of data on

https://support.microsoft.com/en-us/office/split-text-into-different-columns-with-the-convert-text-to-columns-wizard-30b14928-5550-41f5-97ca-7a3e9c363ed7

do any surnames occur more than once?

With the formulas if helpful, assuming your rows are in order

Assuming first name is column B and surname is C currently, add a new column A with header ID (or whatever you want) to both sheets, so first name now C and surname D. Assuming values start in row 2, in cell A2 type =CONCAT(C2,D2)

Copy that formula down for all of your rows, do this in both sheets

In sheet 1, add a column to the end called hair. In row 2 type =VLOOKUP($A2,highlight,5,FALSE)

This assumes hair colour is column 5, just count if not. This is saying look up what is in A2 against the highlighted area (which we know it will now find in column 1), return the value that is in column 5 from that area, and FALSE just means it will look for an exact match of A2 rather than a close match

Don't type highlight, where I've written highlight go into sheet 2 and highlight the full rows and columns. It will end up looking like this '[sheet 2.xlsx]!$A$1:$E$264

Make sure the $signs are in there as these will allow you to copy the formula without it moving the selection range

Repeat for the other columns you want to move, by changing the 5 to 6 for the next column along

Feel free to message me if you get stuck, excel is a bitch sometimes!

Oh wow thanks so much!! I love excel but I've never done lookups. Ex hays and she's just popped in and he's more or less said the same as you Sorry so I'm going to try it tomorrow when I'm back in the office!!

Fingers crossed and thanks again!!

ITS WORKED!!!!!

Thanks so much for all your lookup advice!!!!! I had to tidy up the data bit but phew - it's worked!!!!!

The power of Mumsnet strikes again!!

This is what people are using ChatGPT for.

In order to merge the data from both spreadsheets based on the names, you can use a tool like Excel, Google Sheets, or a programming language like Python. I'll give an example using Excel.

Here are the steps:

1. Open both spreadsheets in Excel.

1. Arrange the columns in both sheets so that the names are in the first column (let's say column A). This will serve as the key column for VLOOKUP.

1. In the smaller spreadsheet (Spreadsheet 1), add new columns for the attributes you want to import from Spreadsheet 2 (blonde, medium).

1. Use the VLOOKUP function to fill in the new columns. If you added "Hair color" and "Size" to columns D and E of Spreadsheet 1, respectively, you would use something like this:

For Hair color:

=IFERROR(VLOOKUP(A2, 'Spreadsheet2'!A:C, 2, FALSE), "")

For Size:

=IFERROR(VLOOKUP(A2, 'Spreadsheet2'!A:C, 3, FALSE), "")

The formula assumes that the name is in column A, hair color is in column B, and size is in column C of Spreadsheet 2.

1. Drag down the formula to fill all the rows for each new column.

1. Repeat steps 3 to 5 for all columns you want to import from Spreadsheet 2.

1. Once you have all the new information in Spreadsheet 1, you can consolidate the information into one column. You would add a new column (let's say column F), and use a formula like this:

``` =A2 & ", " & B2 & ", " & C2 & ", " & D2 & ", " & E2 ``` Drag down the formula to fill all the rows for the new column.

This is the basic approach. Depending on the complexity and cleanliness of your data, you might need to do some additional data cleaning, such as trimming extra spaces or normalizing the capitalization before the VLOOKUP will work as expected.