Combining two lists of data based on two conditions

[jeremybt]

Hi All,

First off, thanks in advance for any help on this. I've been reading/searching the forums for awhile now, but this is the first time I haven't been able to work out a solution to something from other posts, so here I am with post number 1.

What I am trying to do is combine some planned and actual data lists into one reconcilliation.
The plan consists of a material source, a destination, and a tonnage. The actuals consist of the same, but in many cases the plan does not get followed, and the material will not get taken from the correct source, not get placed at the correct destination, or too little/much tonnes are taken.

I would like to take the planned data, and the actuals data, and combine it into one list showing all source and destination combinations(planned and actual, even if only in one or the other), and the coorisponding planned and actual tonnages between each.

This is shown below (sorry for the poor formatting, this was the best I could get it to. The Loc data is under source, Dmp under destination, and numbers under tonnage):

Plan

SRC_P DEST_P TONNAGE_P
Loc_1 Dmp_1 11,000
Loc_2 Dmp_2 12,000
Loc_3 Dmp_3 13,000
Loc_4 Dmp_4 14,000
Loc_5 Dmp_5 15,000

Actual

SRC_A DEST_A TONNAGE_A
Loc_2 Dmp_2 10,000
Loc_3 Dmp_3 13,000
Loc_4 Dmp_4 18,000
Loc_5 Dmp_1 10,000
Loc_6 Dmp_7 9,000
Loc_6 Dmp_6 8,000

Desired Results

SOURCE DEST PLAN ACTUAL
Loc_1 Dmp_1 11,000 00000
Loc_2 Dmp_2 12,000 10,000
Loc_3 Dmp_3 13,000 13,000
Loc_4 Dmp_4 14,000 18,000
Loc_5 Dmp_5 15,000 00000
Loc_5 Dmp_1 00000 10,000
Loc_6 Dmp_7 00000 9,000
Loc_6 Dmp_6 00000 8,000

I hope this clairifies the problem well enough. I am using Excel 2003 SP3, and although I have posted this in the excel forum, I would also be open to a VBA solution to this if it would be the simple option.

Thanks again for your help!

Cheers,
Jeremy

 

[gecs]

With the help of two additional columns is possible, but a little bit complicated solution:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Plan	*	*	*	Actual	*	*	*	*	*	*	Desired Results		*	*
2	SRC_P	DEST_P	TONN._P	*	SRC_A	DEST_A	TONN._A	*	*	*	*	SOURCE	DEST	PLAN	ACTUAL
3	Loc_1	Dmp_1	11,000	*	Loc_2	Dmp_2	10,000	*	Loc_1 Dmp_1	Loc_1 Dmp_1	*	Loc_1	Dmp_1	11000	0
4	Loc_2	Dmp_2	12,000	*	Loc_3	Dmp_3	13,000	*	Loc_2 Dmp_2	Loc_2 Dmp_2	*	Loc_2	Dmp_2	12000	10000
5	Loc_3	Dmp_3	13,000	*	Loc_4	Dmp_4	18,000	*	Loc_3 Dmp_3	Loc_3 Dmp_3	*	Loc_3	Dmp_3	13000	13000
6	Loc_4	Dmp_4	14,000	*	Loc_5	Dmp_1	10,000	*	Loc_4 Dmp_4	Loc_4 Dmp_4	*	Loc_4	Dmp_4	14000	18000
7	Loc_5	Dmp_5	15,000	*	Loc_6	Dmp_7	9,000	*	Loc_5 Dmp_5	Loc_5 Dmp_5	*	Loc_5	Dmp_5	15000	0
8	*	*	*	*	Loc_6	Dmp_6	8,000	*	Loc_2 Dmp_2	Loc_5 Dmp_1	*	Loc_5	Dmp_1	0	10000
9	*	*	*	*	*	*	*	*	Loc_3 Dmp_3	Loc_6 Dmp_7	*	Loc_6	Dmp_7	0	9000
10	*	*	*	*	*	*	*	*	Loc_4 Dmp_4	Loc_6 Dmp_6	*	Loc_6	Dmp_6	0	8000
11	*	*	*	*	*	*	*	*	Loc_5 Dmp_1	*	*	*	*	*	*
12	*	*	*	*	*	*	*	*	Loc_6 Dmp_7	*	*	*	*	*	*
13	*	*	*	*	*	*	*	*	Loc_6 Dmp_6	*	*	*	*	*	*
Sheet1

The first additional column is for listing all the positions on the first two columns in both "Plan" and "Actual" tables concatenated, then in the second additional column these are filtrated to unique values, then this unique values list is used to get the first two columns of the "Desired results" table, by splitting them back using the same separator as for the concatenation (in this case the space character, " "). This is the most difficult part of the process and might be simplified by using names associated to dynamic ranges for the first two columns of the "Plan" and "Actual" tables and for the additional columns. The rest is easy: a couple of SUMPRODUCT based formulas to calculate the values necessary for the remaining two columns in the "Desired results" table.

If you have the the will and the necessary patience to understand and use this procedure I can elaborate on it.

 

[MickG]

Hi, Try this:-
It is assumed that you first list of Data starts "A2" And the second list of Data Starts "D2" (Headers on row 1), the Results Start "I2"

[COLOR="Navy"]Sub[/COLOR] MG02Sep30
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Rng2 [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ray, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Twn [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] a, Res1 [COLOR="Navy"]As[/COLOR] Double, Res2 [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] oRng [COLOR="Navy"]As[/COLOR] Range, Col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
    [COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
        [COLOR="Navy"]Set[/COLOR] Rng2 = Range(Range("E2"), Range("E" & Rows.Count).End(xlUp))
            [COLOR="Navy"]Set[/COLOR] oRng = Union(Rng, Rng2)
                ReDim Ray(1 To oRng.Count, 1 To 4)
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] oRng
                Twn = Dn & Dn.Next
                Res1 = IIf(Dn.Column = 1, Dn.Offset(, 2), 0)
                Res2 = IIf(Dn.Column = 5, Dn.Offset(, 2), 0)
            [COLOR="Navy"]If[/COLOR] Not .Exists(Twn) [COLOR="Navy"]Then[/COLOR]
                n = n + 1
                .Add Twn, n
                Ray(n, 1) = Dn: Ray(n, 2) = Dn.Offset(, 1)
                Ray(n, 3) = Res1: Ray(n, 4) = Res2
            [COLOR="Navy"]Else[/COLOR]
                Col = IIf(Dn.Column = 1, 3, 4)
                Ray(.Item(Twn), Col) = Dn.Offset(, 2)
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] With
Range("i2").Resize(oRng.Count, 4).Value = Ray
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[jeremybt]

Thanks alot to both of you for the solutions; I ended up going with MickG's VBA solution, and it worked perfectly.

If either of you ever find yourselves in Newman, Western Australia, I'll be sure to buy you a round

Cheers,
Jeremy

 

[Fazza]

Hi, Jeremy.

Some other approaches, that might be of interest. A pivot table or a query table. These are non-formula, non-VBA approaches. (Though can be coded.)

Firstly, the pivot table. Say you name the data ranges "Plan" and "Actual". (That is, the headers and the data under. Such as select the entire table and either type the name "Plan" into the name box at the top LHS or hit CTRL-F3 and create that way.) Maybe best to close the file. From a new workbook, menu data, pivot table, external data source. Then 'get data', Excel files, your file, OK, see the named ranges, pick a field or table (to appear on the RHS) - any will do, and continue a few steps until you have three choices and see FINISH. Take the middle option, to edit in MS Query. When MS Query opens, hit the SQL button and replace what you see by text below. Ok to enter, then hit the 'open door' button and complete the pivot table. Resultant worksheet containing the pivot table can be moved into the source data file if you want.

SELECT SRC_P AS [SOURCE], DEST_P AS [DEST], TONNAGE_P AS [PLAN], Null AS [ACTUAL]
FROM Plan
UNION ALL
SELECT SRC_A AS [SOURCE], DEST_A AS [DEST], Null AS [PLAN], TONNAGE_A AS [ACTUAL]
FROM Actual

And, the query table, rather similar. Start via menu data, import external data, new database query. Like before, continue into MS Query and SQL is like below. OK to it and then the 'open door' button to return to a worksheet. This is a query table and refreshable like a pivot table.

SELECT A.SOURCE, A.DEST, Sum(A.PLAN) AS [PLAN], Sum(A.ACTUAL) AS [ACTUAL]
FROM (SELECT SRC_P AS [SOURCE], DEST_P AS [DEST], TONNAGE_P AS [PLAN], Null AS [ACTUAL]
FROM Plan
UNION ALL
SELECT SRC_A AS [SOURCE], DEST_A AS [DEST], Null AS [PLAN], TONNAGE_A AS [ACTUAL]
FROM Actual) A
GROUP BY A.SOURCE, A.DEST

HTH, Fazza

 

[Fazza]

The second SQL I posted does a summation on each of the PLAN and ACTUAL fields.

Though as the sample data doesn't have duplicate SOURCE & DEST values, the (simpler) earlier SQL offered for the pivot table would be OK too.

That is, you can simplify the query table SQL by instead using the SQL offered for the pivot table.

 

[jeremybt]

Hi Fazza,

It does look like a simple and easy to implement solution, and I will certainly make use of it if I decide to have all of this data stored in a DB in the future, but for now I'm looking at keeping it localized to one workbook. Thanks for that though.

Cheers,
Jeremy

 

[Fazza]

OK. Both the pivot table and query table are good contained entirely within the same workbook as the data. This is the approach I'd use, btw - more like a database, no formulas, can be done without code. cheers