Combining 2 lists

[mdr8c]

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!

 

[latarshac]

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

 

[mdr8c]

Thanks, Tarsha - I think you're on to something. I'm working on making the copy happen - then I'll try the filter idea.

 

[facethegod]

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

=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

 

[MickG]

Hi, Something else I'm afraid !!
Data in Rows 1 & 2
Results in Row 12

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

 

[mdr8c]

The macro works great. Thanks so much!!