Consolidate or Combine Columns

  • Thread starter Thread starter Jeff Ray
  • Start date Start date
J

Jeff Ray

Sir/Madam:

Let's say you have two data sets, each with two columns.
The first column in each data set is an ID#. In the
first data set the second column is "height" and in the
second data set the second column is "weight"

DATA Set 1 Data Set 2
ID# Height ID# Weight
1 57 1 167
2 60 2 190
3 50 4 122
5 160

I want to combine (merge, consolidate) these into one
data set:

ID# Height Weight
1 57 167
2 60 190
3 50
4 122
5 160

You can see from the above that some ID numbers occur in
both data sets (i.e. there is a height and weight for
that entry) or in some cases for an ID there is only a
weight OR a height.

Of course this is only a small sample to illustrate the
problem, acutally I have thousands of heights and weights
that I need to consolidate by ID number. The consolidate
function does not seem to do this.

Any ideas?

Thanks,
Jeff
 
Jeff,

You could copy both columns of ID #'s into one list and use
Data|Validation|Custom to create a list of Unique ID #'s. Then use VLOOKUP
to lookup the heights and wieghts from the separate tables.

If you use the VLOOKUP in an IF statement you can eliminate the errors when
the height/weight data is not available.

IF(ISERROR(VLOOKUP("id","height table range"),2,FALSE)),"whatever you
want",VLOOKUP("id","height table range"),2,FALSE))

PC
 
If the ID#'s are not duplicated you can use the vlookup or
hlookup functions for this it will look up an ID number,
and put in the number from the height or weight column.
And or you can use the "concatenate" function one of these
if not both will do what your looking for.
 
You are right Dave. I did mean to recommend data filter. Thanks for
posting the correction.

PC
 
Back
Top