Merging data from two sheets?

[caleb2003]

Hi, I have 2 large (100k+ rows) Spreadsheets, where one has lots of information including each entries state and City and I also have another spreadsheet which has city and postcode information.

I need to get the postcodes into the main sheet that already has the cities but not the postcodes.

Is there a way to import this seeing as each sheet contains fields of similar data (ie. the city information)

Sheet 1 example fields:
Name, description, state, city
Sheet2 fields:
State, City, Postcode

I want the postcodes in sheet 1 preferably without doing it manually!!

Thanks

[teylyn]

Hi,

you should be able to use VLOOKUP for this, but only if there is only ever one post code per city. I imagine that a place like Sydney would have more than one post code, or not?

maybe you could post a small sample of your data and we can take it from there.

Click "Go Advanced" below and then the paper clip icon to attach a file, but make sure it's not too big.

(Kiwi on a data plan)

[caleb2003]

Hi, the 'city' field is more like area ie sydney west or whatever.

I have attached a simple file with the 2 sheets in where the pcode data needs to go into sheet 2, you got me thinking about this and I wondered if there is a way to check against also the 'State' field incase of any duplicate names form different states?

hope that makes sense.

[teylyn]

Hi,

try this in C2 on Sheet 2 and copy down. Adjust the number in the red bit to be the last row with data in Sheet1

=VLOOKUP(TRIM(B2),Sheet1!$C$2:$D$8,2,FALSE)

This formula takes the text from cell B2, strips if of the leading space (!!), then tries to find the text in column C on Sheet1 and if a match is found returns the value from column D in Sheet 1.

The assumption is that area names are unique on Sheet1. If an area is not found on Sheet1 (as is the case in the short data sample), the formula will return #N/A


cheers

[teylyn]

With regards to the same area name existing in more than one state, it would help if the State codes on sheet 1 were the same as on sheet 2 or vice versa.

You could do a Find and Replace to ensure that "New South Wales" is replaced with "NSW" and so on, then insert a helper column in sheet 1 before Pcode, with the formula

=B2&C2

copied down.

then on sheet2 you can use

=VLOOKUP(D2&TRIM(B2),Sheet1!$D$2:$E$8,2,FALSE)

See attached

[caleb2003]

THanks I will try it now!

[caleb2003]

Hello, I can get this working on the sample sheet but struggled to on my original sheet with more data and have worked out that it is because the data on the main sheet (sheet 2) is not in the same order as the data on sheet 1, reason being because sheet 2 has many more postcodes and places than my main data file (sheet 1) has.

So if the values are in different cells is there a way this formula can lookup data from the whole column to find a match?

Thank you

[teylyn]

Hi again,
not quite sure I understand the problem. VLOOKUP will look in the range that you specify. Taking my first formula

=VLOOKUP(TRIM(B2),Sheet1!$C$2:$D$8,2,FALSE)

if your list of post codes is 1000 rows, then you have do adjust the red number to be


=VLOOKUP(TRIM(B2),Sheet1!$C$2:$D$1000,2,FALSE)

VLOOKUP will find the search value even if the list is unsorted. That is what the 'FALSE' argument ensures.

But if the post code list has more than one area with the same name, then VLOOKUP will return only the first match. In this case, you'll need to jump through the hoops I explained in my later post, combining state and area to create unique list entries in a helper column.

could you maybe post another example that illustrates where the solution goes wrong?

[caleb2003]

You were right again, it was that amount for the postcodes I needed to amend, so I put in the full number of rows (13171) and now it works!!!!!

Thankyou again, much appreciated

[caleb2003]

Aaargh, I did this and it worked and then found out I was working on the wrong spreadsheet!!

The correct one has all these entries (minus the postcode column) plus 30k more rows, is there any way of dragging this data into the other sheet by cross referencing one of the fields and replacing the contents? (ie the main unique identifier between each entry is the url so can the url be checked from sheet one and pull the whole row into sheet 2 replacing the existing entry with the extra data created from the exercise previously discussed?

[jj72uk]

If you have Access, wouldnt importing the tables into Access and running a query do the job?

[teylyn]

Caleb,

sorry, it's too late for me to think logically. Can you create a small sample of the worksheet that you should have been using so I can have a look at how to set up the formulas (in the morning)

cheers

[caleb2003]

I'll do it tonight, sorry for the headache!!!

[caleb2003]

Hi, attached 2 files, smallfile .xlsx which is the same as the one you provided to create the posctodes and also biglist.xlsx which is similar to the list I am trying to update but need the information from smallist.xlsx importing in.

As you can see there are more fields in biglist yet they have the same entries (the main unique identifier being the name and/or the url).

I should have worked on biglist in the first place and wouldnt now have this problem but I cannot just apply the previous formula to biglist as I cleaned up thousands of entries in smallist (or the equivalent file) to make it work.

Hope that makes sense!

Regards

[jj72uk]

I'll go back to what I said a few posts up..

You can import the 2 tables into Access and run a query to 'merge' them

Once you have done you're VLOOKUP from sheet 1 to get the relevent data, imoprt the small sheet and big list into access, define the relationship ( in this case it would be the name)

Run the query and export the sheet back out to Excel.

Attached it the ran query and access DB

[teylyn]

JJ, that makes a lot of sense and is probably the quickest way to go about it.

You cool with that, caleb?

[caleb2003]

Hi, I've been trying this but must admit I am struggling having never used access before, I am using the 2 examples I posted on this thread to test (as it was taking forever with my main lists) and have imported them into access using the external data (from Excel) import tool putting both into their own tables with the field called 'name' as the primary key, then I added the relationships, then running the query I do get the info merged but with all fields from both tables displying in big table rather than for instance the Area field in sheet 2 displaying along with postcodes etc.

When I did it, I run the simple query wizard so not sure if this is why its not going right for me.

I will keep trying, thanks to you both for your continued help.

[jj72uk]

Once you have set the primary relationship - In this case I set as field 1 - there names you'll need to edit the query manually if you ran the wizard.

Right click on the save query in access and choose Design View

Once in design view on the lower half of the new screen will be what fields are displayed when the query is ran. Just unselect the duplicate fields.

For example, you'll keep the name field checked for sheet 1 but unchecked for sheet 2 and so on.

[caleb2003]

HI, have done this but the problem is that I have sheet 1 which has more data than sheet 2 but sheet 2 has the right data, so for instance the name field in sheet 1 has more entries but I wish the name field in sheet 2 to be the one shown in the query (to take precedence over sheet1s name field data) but also include the additional entries from sheet 1.

does that make sense?

Thanks again

[jj72uk]

Caleb,

If the data isnt to sensitive could you post it and I will take a look.

Just to confirm, you only want the names matched from sheet 2, even if there are more names on sheet 1

[caleb2003]

HI, I'm sorry but it is sensitive, but in the examples we have worked off up to now that is what I have been testing with and simply adding a few extra entries to sheet 1 as well as changing slightly the name field data on some fields of sheet 2 (like the spelling) will duplicate what I am referring to.

So in the scenario I just mentioned when merging the two sheets by running the query I cannot get it to give precedence to one sheets field and also append any extras from the less important sheet (that has more data).