two lists

B

bazblazer

I have two lists in separate worksheets. The data is almost identical.
There is a column for ID and a column for a post code. I need to be
able to put all the data into one sheet with three columns. One will
list the the IDs and the others will put the two postcodes next to
each other. Unfortunately, some IDs only appear in one sheet, some in
the other and some are in both. So I will have some records complete
and some partial complete.

I have tried to use a look-up but I have confused myself. help!
 
E

Earl Kiosterud

bazblazer,

I'm not sure I've actually heard a question. I've made an example. If
you're not using a fixed-width font like Courier, this may not line up well
in your news reader.

Sheet 1
ID Post Code
A 1
B 1
C 2

Sheet 2
ID Post Code
A 1
B 2
D 4

Is below the output you want:
ID Post Code1 Post Code 2
A 1 1
B 1 2
C 2
D 4

If so, try Data - Consolidate. Check "Use labels in" both "Top row" and
"Left column." If not, change the example so it shows all possible
circumstances, and the output you want.
 
D

Dave Peterson

Another option...

I'd add a new sheet.

Copy the values in column A of sheet1 to A1 of this new sheet.
Then copy the values in column A of sheet2 under the last entry of that new
sheet (in column A).

Then add a header in A1 (insert a new row if you need to).

Then use Data|Filter|Advanced filter to get the unique entries and plop those
into B1.

Debra Dalgleish shows how:
http://www.contextures.com/xladvfilter01.html#FilterUR

Then use =vlookup()'s to return each of those amounts in column B and C.

In B2:
=if(iserror(vlookup(a2,sheet1!a:b,2,false)),"",vlookup(a2,sheet1!a:b,2,false))

In c2:
=if(iserror(vlookup(a2,sheet2!a:b,2,false)),"",vlookup(a2,sheet2!a:b,2,false))

Debra Dalgleish has some notes:
http://www.contextures.com/xlFunctions02.html (for =vlookup())
 
Top