Excel, merge two sheets with one column as key

1

Let me start by saying I exclusively use Excel to view things and that's where my experience ends.

I have two sheets with some common data, and most importantly, a column in each that has the same key (format is 0.xxxxx where "xxxxx" is a number from 1 to 12000). Each sheet has some unique data - for example one sheet has names, the other does not. I want to essentially merge these (non-destuctively) into a single sheet. I've tried some options with "VLOOKUP", but I'm not really understanding the nuances of this. I assume this is probably a one-button operation as it seems like a fairly common database-like thing to do (in fact last time I had to do this I dumped the spreadsheets into postgres and dealt with it there).

sporker

Posted 2012-07-02T23:37:40.700

Reputation: 43

well you may be able to do something, but what do you want? Another sheet with all of the columns combined? Or a couple of each? you need to be more specific – datatoo – 2012-07-03T01:58:11.953

Yes, I need the data combined in a single sheet, matched on one column that has a common identifier between the two sheets. – sporker – 2012-07-03T23:00:27.310

Answers

1

The summary sheet contains each field in both Sheet1 and Sheet2 in whatever order you want. the vlookup formula for each field is adjusted for the field you want displayed

enter image description here

enter image description here

enter image description here

datatoo

Posted 2012-07-02T23:37:40.700

Reputation: 3 162

I kind of see what's happening there, but not completely. I ended up using something like this: =VLOOKUP($A2,'nni-pvcrep'!$A$2:$F$300,1,FALSE). To be honest, I had a really, really hard time finding any explanation of the table array. What I have there seems to work to match column A on one sheet with column A on another and then pull in the next column (B). IIUC, the "F" refers to the farthest column in the first sheet I want to look at, the 300 refers to the maximum rows to search down in the first sheet and the "1" is one column to the right of my first sheet match. – sporker – 2012-07-06T03:45:10.557

vlookup should indeed do what you want. This example above shows the formula for B2 below it in B3, C2's formula as C4, so this example shows the results in row two with the formula directly below in the appropriate column, so you can see how the ranges are adjusted for different sheets. Good luck – datatoo – 2012-07-06T13:34:08.383

Asked: 2012-07-02T23:37:40.700

Viewed: 9 742 times

Active: 2014-04-28T15:09:13.793