Combining 2 lists

mdr8c

Board Regular
Joined
Feb 7, 2006
Messages
93
I have 2 rows of data and want to combine them into 1 row. Any values that are not in the 1st row but are in the 2nd should be included in the result row, and vice versa. For example, if I start with these 2 rows:


00 07 08 09 1 13 14 15
00 07 09 1 13 14 15 16 18


Then I would like the 3rd row to look like this:

00 07 08 09 1 13 14 15 16 18

How would I go about this? VLOOKUP? MATCH? Something else?
Thanks for your help!
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
I dont know if this is the most advanced method but I would combine both rows then do a unique filter to only select those that are not duplicate. I would then copy the new unique list to a new row, then unhide your rows, select the new row and copy/paste values to remove the formulas.

From there you will have a unique list that contains numbers from both rows.

Tarsha
 
Upvote 0
Thanks, Tarsha - I think you're on to something. I'm working on making the copy happen - then I'll try the filter idea.
 
Upvote 0
I have 2 rows of data and want to combine them into 1 row. Any values that are not in the 1st row but are in the 2nd should be included in the result row, and vice versa. For example, if I start with these 2 rows:


00 07 08 09 1 13 14 15
00 07 09 1 13 14 15 16 18


Then I would like the 3rd row to look like this:

00 07 08 09 1 13 14 15 16 18

How would I go about this? VLOOKUP? MATCH? Something else?
Thanks for your help!


Row1-A1:H1
Row2-A1:I1
Try this in Row3

Code:
=IF(COLUMN(A1)-COLUMN($A$1)+1>COLUMNS($A$1:$H$1),INDEX($A$2:$I$2,,SMALL(IF(ISNA(MATCH($A$2:$I$2,$A$1:$H$1,0)),COLUMN($A$2:$I$2)-COLUMN($A$2)+1),COLUMN(A1)-COLUMNS($A$1:$H$1))),INDEX($A$1:$H$1,,COLUMN(A1)-COLUMN($A$1)+1))

This is an array formula so it must be entered with Control + Shift + Enter; Not just enter

HTH.

Thanks
 
Upvote 0
Hi, Something else I'm afraid !!
Data in Rows 1 & 2
Results in Row 12
Code:
Dim rCells As Range, rCell As Range, vUniques As Variant, oNu As Integer
Dim rng2 As Range, rng1 As Range

Set rng1 = Range(Range("A1"), Cells(1, Columns.Count).End(xlToLeft)) 
 Set rng2 = Range(Range("A2"), Cells(2, Columns.Count).End(xlToLeft))
   Set rCells = Union(rng1, rng2)

With CreateObject("scripting.dictionary")
    For Each rCell In rCells
        If rCell.Value <> "" Then _
            If Not .exists(rCell.Value) Then .Add rCell.Value, ""
            Next rCell
    vUniques = .keys
oNu = UBound(.keys) + 1
End With
  Range("a12").Resize(, oNu).NumberFormat = "@"
 Range("a12").Resize(, oNu).Value = vUniques
Regards Mick
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,240
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top