I have a set of spreadsheet data taken at time T1. Two columns A:B. Col A holds rows of 4 digit reference, Col B with £ values.

I now have another set of data taken at T2 with same format. There may be more or less rows compared to data at T1.

I would like to list the row data that matches between T1 and T2 by:

(1) the 4 digit reference only

(2) both the 4 digit reference and value

(3) data that does not match

I hope I make sense. Any help would be much appreciated

PS - formulas I'm happy with; VBA you'll need to go gently!

Thanks

Michael Ung

## Replies

## Please login or register to join the discussion.

why excel?

This is actually much easier using a database. If you have Access then you can link to the two spreadsheets, and then you can use the query builder to build queries that will provide the answers you are looking for.

If you don't have Access then the pivot table route is a database type approach but with less functionality, and probably a similar amount of learning.

If you really want to use formula, then you can use text concatenation to create a key field from reference and value, and then a vlookup formula to match, but you will have to be careful with rounding between the two sheets for the values.

A bit more info to understand

Is the data T1 and T2 on the same or different sheets (or even different workbooks)?

Are the 4 digit refs unique in each list or can they appear more than once with a different amount?

Do you want cell references for matches / no match in each or a sum of amounts, or...

What are you trying to achieve with the resulting list?

Sorry loads of questions, no answers

Use match function

For example, with your data in two sets of columns, starting in row 2 (put headings in row 1)

Col A:B, and to compare with data in

Col G:H

Add in Col C and I:

=A2&B2, and =G2&H2, for every row in each section

This creates a column which combines text and numbers

Add in col D, row 2 and copy down:

=ISNUMBER(MATCH(A2,$G$2:$G$16,0))

Add in Col E, row 2 and copy down:

=ISNUMBER(MATCH(C2,$I$2:$I$16,0))

The cells which find matching data in cols D and E will show TRUE, and those which don't match will show FALSE. (The Match function result is a number when matched, and the isnumber function returns True or False, which makes filtering easier in the next step).

Now turn on data filter, and select TRUE from the relevant column filter in colum D or E (one at a time) to list only the matching entries, or FALSE to show the non-matching entries.

This can be done with data on separate worksheets, or in separate workbooks, adjust your ranges to suit.

Hope this is helpful

Regards

Mike

To get a new list excluding matches you could....

using a new sheet or the T1 sheet:

copy and paste the T2 data underneath the T1 data. (see final step first - if you want extra info).

Then in column C calculate AxB. Then copy to all rows. If A contains any non-digits or the products are too large, then concatenate (as Mike (1) suggested).

Sort all data, inc extra info if required, based (say low to high) on col A.

In column D of row 2 (ie the 2nd row of the table)use [=] IF((A this row=A row above)AND(C this row=C row above),"match")

The inverted commas are part of the formula -if there is no match col D will be blank. Then copy to all rows below.

NB If answer of the type (3 x £4) =12, or (2 x £6) = 12 might apply this would eliminate false maches in col C; but If this could not happen just do the IF test on col C.

Then copy col D to itself as values.

Then sort all data based on col D (say low to high).

Then delete all the bottom rows with the word match in col D.

Then delete cols C and D. Then save as ...

----------------------------------------------

If you want to identify where each entry came from then copy T1 (or a date) to each row in say, Col E and T2 (or a date) after you copy all T entries below and then in col E copy T2 to all T2 rows.

I have 3 spreadsheets, other than Excel, so Mike (1), if he thinks it worthwhile, may add relevance to my suggestion.

try pivot tables - multiple consolidation ranges

For a different approach, try using pivot tables with the Multiple Consolidation Ranges option.

This allows you to drop 2 similar spreadsheets on top of each other and use a pivot table to see where they agree or differ.

For a discussion, start with para 5 of Consolidation with Pivot Tables

The multiplication technique,,,

Suppose the data starts in row 6 (after sorting the merged list on col A first choice and col B second choice):

If a7 = a6 put a 1 in c7

If b7 = b6 put a 1 in d7

Multiply c7 by d7 with the result in e7

(a double match gives a 1 in e7 else 0)

If you wish to do more than merely view, after copying down the formulas in c7, d7 and e7 copy all results in cols C to E (over themselves) to values before performing any sort.

PS David, I found your alternative solution interesting.

Alternatively...

If you enter the pass book sheet ref no. in Col C as you go and as cheques etc clear and in Col D copy the formula If c this row >0, b this row.

Then you could enter the receipts above, below or to the right of the payments and also enter the pass book sheet number as they clear.

If you sum the columns upward and use the horizontal hold, you can auto rec the bank and have an audit trail and you will "immediately" spot any error.

Thanks you and feedback

Alan: Thanks - T1 & T2 can be in same workbook. I am seeking to achieve something along the lines of say a bank rec. Say Col A is the cheque number and Col B is the £, T1 is the cashbook and T2 the bank statement, the results required are:

(1) list all matching (cleared at bank) items

(2) list all unmatched items (uncleared at bank)

David: Thanks, will certainly look at your link.

Mike 1 & 2 - Thanks, I'll give it a go.

Alastair - Thanks, I had actually begun to take the concatenation and vlookup route but felt there was an easier way, hence my appeal for help. Agreed Access would be the way forward on this, once I am up to speed on this.

## Please login or register to join the discussion.