MATCHING UP OF MULTIPLE SHEETS.....Pls Pls Pls Help.............

[samuella]

Excel Gurus,

Pls pls pls help.I'm in the middle of of course work and stuck. (!)...Not sure what to do now... I have an EXCEL sheet that contains a Master Sheet and Sub Sheet's (made of month's of the year). Both MASTER AND SUB contain Voucher Numbers, dates of voucher transaction and amounts.

Basically i need to MATCH/SEARCH/LOOK UP (whichever) works best the MASTER SHEET against the SUB-SHEET (months of the year) to tell me all matches OF THE VOUCHER NUMBERS and if possible HIGHLIGHT THEM??. That will therefore leave me with a WORKBORK which shows non-highlighted vouchers. (Then i can conduct investigations into the non-highlighted ones..). Until now i have bn using FIND AND SEARCH which for such a huge database its really not helping.

I have only used samples in this case as actual is huge. Over 500,000 vouchers to be matched up.

I hope this makes sense. Pls find sample sheet of case study attached.
Many thanks in advance for the help. Most appreciated!

[mike7952]

@samuella

I dont think there is a formula that will do what you want, however a macro can get it done.

Example.xlsm

Please Login or Register  to view this content.

[Richard Buttrey]

The easiest approach is to keep all your data on one sheet, with a fourth column to identify the month.

If you do that analysis is much simplified.

[Max, Singapore]

This idea uses COUNTIF to check for the existence of the vouchers in each of the month sheets, with INDIRECT enabling great ease in formula propagation

In MASTER SHEET,
List the month sheetnames in E1 across to M1, viz: Jan, Feb, .. Sep
Take care that these names match exactly (except for case) with the actual names on the tabs

Put in E2: =IF(COUNTIF(INDIRECT("'"&E$1&"'!A:A"),$A2),"x","")
Copy E2 across to M2, fill down all the way. This flags where the voucher num is found in which sheet

Then drop this into D2, normal ENTER to confirm will do:
=INDEX($E$1:$M$1,MATCH(TRUE,INDEX(E2:M2="x",),0))
Copy D2 down all the way to extract the name of the month sheet where the voucher is found. Filter col D for #N/As, which signals unmatched vouchers to grab it all in one stroke

[samuella]

Thanks Mike, exactly the solution i wanted! Most appreciate!!!
@ Richard, thank you too for input.
@Max, thanks and appreciate as that option also seems to work. Will drop a question later.

Samuella

[samuella]

Mike,

Sorry a quick question: Will it be possible to also get THE MASTER SHEET to also show VOUCHERS that are not found in any of the Sub Sheets (Months of the Year)? Currently what ive just realised is that all the Vouchers in the MASTER SHEET are been highlighted however there are some of these vouchers that do not appear in any of the months of the year. Hope this makes sense. Many thanks and do appreciate!

Samuella

[mike7952]

@Samuella

Try this

Please Login or Register  to view this content.

[samuella]

@ Mike,

Thank you laods! Really appreciate!!!! U are a diamond......

[samuella]

@ Mike,

Sorry, how do i add on more sheets ie October, November , December? Thanks

[mike7952]

Where you see sept you will see a little * next to the sheet. Just click on it and rename the sheet.

[samuella]

Thank you!!!!