Combining Data Sets

[LazyLarry]

I have two sets of data which have a common field but also a unique item which I would like to combine into one data set.
EG.
Branch A has: Branch B has:
Item# Qty Item # Qty
1 2 1 3
2 5 2 4
3 6 4 8

which I would like to combine into the following:
Item# Qty.A Qty.B
1 2 3
2 5 4
3 6
4 8

So their could be unique items in both sets.
Is there a function for doing this?
Ultimately I want to create a macro that can import two text files (variable number of rows) and combine them.
Thanks.

 

[gmhumphr]

For a quick solution to the problem (that requires a couple steps on your part), consider a pivot table:

1. Using cut/paste, stack your data into a 3 column set: Branch, Item #, Quanity. Specifically, add a column A, copy item# and quanity from branch B and paste it under the same data for branch A, add the branch identifier in column A as appropriate.
2. Run a pivot table on your newly formatted (now tabular) data.
3. Put Item # in rows, Branch in columns, and Quantity in aggregation.

The result is a distinct list of item numbers, and the quantity held by each branch.

If you want a more push-button solution, I defer to another poster. Perhaps there is something that can be done efficiently with PowerQuery, or could be written in VBA. You might want to include details on whether there are more branches than just A and B.

 

[Peter_SSs]

Ultimately I want to create a macro ...

See if this code could be part of your solution.

Do any testing on a copy of your data.

Sub Combine()
  Dim a As Variant
  Dim d As Object
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = a(i, 2)
  Next i
  
  a = Range("D2", Range("E" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    d(a(i, 1)) = d(a(i, 1)) & "|" & a(i, 2)
  Next i
  
  With Columns("G:I")
    .Rows(2).Resize(d.Count, 2).Value = Application.Transpose(Array(d.Keys, d.Items))
    .Columns(2).TextToColumns DataType:=xlDelimited, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="|"
    .Rows(1).Value = Array("Item#", "Qty.A", "Qty.B")
    .AutoFit
  End With
End Sub

The code above produced columns G:I below from the sample data in columns A:E

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I
1	Item#	Qty		Item #	Qty		Item#	Qty.A	Qty.B
2	1	2		1	3		1	2	3
3	2	5		2	4		2	5	4
4	3	6		4	8		3	6
5							4		8
Combine

 

[LazyLarry]

Thanks I will try both of those methods. I tried to use msquery to query the two tables and do a full outer join but the join didn't work and still excluded data.

 

[Fazza]

For a query, consider a UNION ALL query to make a single dataset, and then TRANSFORM to get the result. Something like, untested,

TRANSFORM SUM(Qty)
SELECT Item
FROM (SELECT 'A' AS [WhichOne], Item, Qty
FROM tblA
UNION ALL
SELECT 'B', Item, Qty
FROM tblB)
GROUP BY Item
PIVOT BY WhichOne

Modify to suit your table & field names. No code, no formulas and should be fast even with large datasets. cheers

 

[LazyLarry]

Hi, Ultimately I have used MSQuery to have excel query itself. I have joined the two sets with a Full Outer Join as follows:
SELECT table1.a, table1.b
FROM table1
LEFT OUTER JOIN table2
ON table1.a = table2.a
UNION
SELECT table2.a, table2.b
FROM table2
LEFT OUTER JOIN table1
ON table1.a = table2.a
WHERE table1.b Is Null
OR table2.b Is Null

Works very well other than it brings a full blank line in at thee top of the query.

 

[Fazza]

Good work, Larry.

That looks like it just returns two fields - a & b.

Not the cross tab like I thought you wanted? Item, QtyA, QtyB
The SQL I posted (for use in MS Query) is supposed to match what you asked for.
I haven't time to check today but if you want I can have a look in the coming days.

regards, Fazza