Hi All. This is my first time posting. I have a question regarding a task that I need to complete quite urgently. I am reasonalbly familiar with excel and use most of the more commonly-used function and formulas. However when it comes to macros, i have had limited success, not having a true developer background. Since the task I need to perform cannot be done using a standard excel function(i'm pretty sure) I believe the only solution would involve some code, i.e. a macro. So at a basic level I have to compare two recordsets. One from PROD and one from UAT. The objective is to establish any missing records from BOTH lists... and therein lies the complexity. Since its not a given that either list is complete I have to check both ways. To add to the complexity, the list is built hierarchically so it is not a flat comparison i.e. the first 10 records/rows may match on LEVEL1 (e.g. A-A; B-B; C-C) but not on LEVEL2 (A1-A1;A2-(no match); etc. THE EXCEL PART I have placed the two recordsets side by side on a single worksheet. So lets say the first couple of records match exactly so the rows are perfectly aligned next to each other. Then, because there are more records in PROD at LEVEL2 of the hierarchy the rows start misaligning (this works both ways where there are more records at LEVEL2 for UAT). What I am trying to do as a first step is to align all records based on LEVEL1 of the the hierarchy. Put another way I want Excel to dynamically take the value (and corresponding row) from the one list e.g. the PROD list and line it up with the matching value from the UAT list. But here is the kicker. Excel must be able to determine for any particular LEVEL1 value whether there are more records in UAT or in PROD so that it aligns the records from either side. I have tried to give as much information so that you have as clear a picture as possible without completely confusing you. I fear that I have failed but hopefully not. I will answer any questions to clarify. I can also post a small subset of the actuall records from excel if that will help with visualisation. I have pasted the recordset below. It contains both sets of information (PROD and UAT) side by side as I described in my email. I have added the column letters(A-U) in the first row only in case it helps with alignment but they are not part of the recordset and can be removed. The subset for UAT ends at column J and the subset for PROD begins at column L. The space (what would be column K) can be removed. COLUMN A would represent the first level of the hierarchy (2 records i.e. "CURRENT ASSETS" and "DEP W/OTHER BKS OPER. BAL NIB"). COLUMN B and C would represent the second level of the hierarchy (3 records for "CURRENT ASSETS" i.e. "N050099","N100099","N150599" and their accompanying description. COLUMN D-J would represent the third and final level with one row/record for "N050099", one row/record for "N100099", and two rows/records for "N150599". This is what I need to compare to the second recordset (beginning at COLUMN L) which is laid out identically except that they do not always have the same amount of records per hierarchical level e.g. "N050099" in COLUMN M has 3 rows/records as opposed to "N050099" in COLUMN B which has only one row/record. The added complexity is sometimes there are more records in PROD and sometimes there are more records in UAT. I imagine that if there were always more records in the one then the code would also be a lot simpler. Anyway hope this all makes sense (or at least most of it). Feel free to ask questions. THANKS!

re: I have pasted the recordset below. It contains both sets of information (PROD and UAT) side by side as I described in my email. Not that I can see...

Hi, A macro to do this would IMHO be quite complex and would take a long time to write and test. As an alternative you might consider the following: 1. Create a 'master' list by combining both sets of data 2. Use the Data-Filter-Advanced filter function (using copy to another location and unique records only options) 3. With this master list use two groups of columns to identify matches or missing data: 4. Use the Vlookup function to lookup master values in each of the PROD and UAT source records. 5 Error values from Vlookup will highlight missing entries in both groups. (If you combine Iserror with Vlookup you can get a better looking output, rather than error values) Without seeing the data I don't know if this will work, but as a concept it should be easier to achieve than writing a macro. Regards

Thanks for both replies. I like your suggestion Humar and will play around with the idea a bit and see if that will be a good fit. About the sample data. I just copied and pasted this post from another forum that I used. I was able to post a structured sample there but it completely fell apart when I pasted it here. It does not seem like I can attach a file so not sure how to get a sample to you which I could send in excel format. So if you are willing, PM your email and I will email you the sample. Thanks for your help!

You can format a sample data set by using the pre tag found above Comments box. A B C 1 Data_1 Data_6 Data_11 2 Data_2 Data_7 Data_12 3 Data_3 Data_8 Data_13 4 Data_4 Data_9 Data_14 5 Data_5 Data_10 Data_15 You can also preview your message (multiple times) until you like the way it lines up. Before you click Confirm, check the box next to: Check To Show Confirmation Page Again to Preview your post again and again and again.

OK. This should do it. If you copy the sample below to notepad (to get rid of tags and formatting) and then copy that to excel it seems to line up pretty well. Also if you set the font to courier new size 8 that helps visually and its pretty much what im looking at over here. A B C D E F G H I J K L M N O P Q R S T U RPT LINES G/L ACCOUNTS RPT LINES G/L ACCOUNTS A&L SUM A&L LINE DESCRIPTION ACCT MIN ACCT MAX PROD MIN PROD MAX CUST MIN CUST MAX RES TYPE A&L SUM A&L LINE DESCRIPTION ACCT MIN ACCT MAX PROD MIN PROD MAX CUST MIN CUST MAX RES TYPE CURRENT ASSETS CURRENT ASSETS N050099 GOLD COIN & GOLD/SILVER BULLION - US$ N050099 GOLD COIN & GOLD/SILVER BULLION - US$ 0100 1006 80120 80121 E000 E999 ALL 0100 1006 80120 80121 E000 E999 ALL N100099 OTHER COIN OUTSIDE CANADA 1010 1010 80120 80121 E300 E300 ALL 0100 1006 80105 80115 E000 E999 <>CA 1010 1010 80120 80121 E901 E901 ALL N150599 GOVERNMENT AND BANK NOTES N100099 OTHER COIN OUTSIDE CANADA 0100 1006 80050 80050 E000 E999 ALL 0100 1006 80105 80115 E000 E999 <>CA 0100 1006 80100 80100 E000 E999 ALL N150599 GOVERNMENT AND BANK NOTES DEP W/OTHER BKS OPER. BAL NIB 0100 1006 80050 80050 E000 E999 ALL N200599 DEP W/OTHER BKS ACCEPTANCES 0100 1006 80100 80100 E000 E999 ALL 1000 1010 01920 01920 E800 E803 ALL DEP W/OTHER BKS OPER. BAL NIB N201099 DEP W/OTHER BKS TERM DEPOSITS N200599 DEP W/OTHER BKS ACCEPTANCES 1000 1003 01961 01962 E800 E803 ALL 1000 1010 01920 01920 E800 E803 ALL 1000 1010 01930 01939 E800 E803 ALL N201099 DEP W/OTHER BKS TERM DEPOSITS 1000 1010 01941 01951 E800 E803 ALL 1000 1003 01961 01962 E800 E803 ALL N201399 DEP W/OTHER BKS TERM DEPS TRDG 1000 1010 01930 01939 E800 E803 ALL 1000 1010 01940 01940 E800 E803 ALL 1000 1010 01941 01951 E800 E803 ALL 1000 1010 04712 04712 E800 E803 ALL N201399 DEP W/OTHER BKS TERM DEPS TRDG 1200 1209 04712 04712 E800 E803 ALL 1000 1010 01940 01940 E800 E803 ALL N201599 GOLD /SILVER DEPOSITS US$ 1000 1010 04712 04712 E800 E803 ALL 1000 1006 80130 80131 E000 E901 ALL 1200 1209 04712 04712 E800 E803 ALL N202099 OTHER DEPOSITS - OPERATING BALANCES - INTEREST BEARING N201599 GOLD /SILVER DEPOSITS US$ 1000 1000 01910 01910 E800 E803 ALL 1000 1006 80130 80131 E000 E901 ALL N202399 BANK OF AMERICA N202099 OTHER DEPOSITS - OPERATING BALANCES - INTEREST BEARING 1000 1000 88100 88100 E800 E800 ALL 1000 1000 01910 01910 E800 E803 ALL 1000 1000 88101 88101 9996 9996 ALL N202399 BANK OF AMERICA 1000 1000 88101 88101 9997 9997 ALL 1000 1000 88100 88100 E800 E800 ALL 1000 1000 88101 88101 9998 9998 ALL 1000 1000 88101 88101 9996 9996 ALL N202599 DEP W/OTHER BKS OPER. BAL NIB 1000 1000 88101 88101 9997 9997 ALL 1000 1000 01911 01911 9033 9033 ALL 1000 1000 88101 88101 9998 9998 ALL 1000 1000 01911 01911 E800 E803 ALL N202599 DEP W/OTHER BKS OPER. BAL NIB 1000 1006 01911 01911 E305 E305 ALL 1000 1000 01911 01911 9033 9033 ALL N203599 FEDERAL FUNDS SOLD US$ 1000 1000 01911 01911 E800 E803 ALL 1000 1006 80335 80335 E000 E999 ALL 1000 1006 01911 01911 E305 E305 ALL N203599 FEDERAL FUNDS SOLD US$ DEP W/OTHER BKS OPER. BAL NIB 1000 1006 80335 80335 E000 E999 ALL

Hi, What I would do is to create 'complete' rows of data. The layout you have is fairly typical with headings and subheadings, which are not shown on subsequent lines until the subheading or heading changes. This means that when you look at an item at the lowest level, you don't have its full description. For example the last line in the first block is: 1000 1006 80335 80335 E000 E999 ALL To be complete this should be: DEP W/OTHER BKS OPER. BAL NIB N203599 FEDERAL FUNDS SOLD US$ 1000 1006 80335 80335 E000 E999 ALL Once all of the lowest level items have their full description, sorting or comparing becomes easier. To create all the unique sets of data, insert three columns before the start of the lowest item data, i.e., before Acct Min In my worksheet these are columns D, E & F In column D starting on the same row as the first highest level heading (CURRENT ASSETS) enter the following formula: =IF(A3<>"",A3,D2) "Current Assets" is in cell A3 D2 is empty Drag this formula down and each row will now have its highest level heading. In Column E starting on the same row enter the formula =IF(B3<>"",B3,E2) and column F =IF(C3<>"",C3,F2) Drag each formula down The rows for the lowest level items will now have all their higher levels shown on the same row Now create single concatenated items: Add a new column after the last column of items (RES TYPE) - (column M in my case) Make it wide so that you can see whats going on! Starting at the top enter the following formula in the new column (same row as you started the other formulas) - N3 in my case =IF(M3="","",D3&":"&E3&":"&F3&":"&G3&":"&H3&":"&I3&":"&J3&":"&K3&":"&L3&":"&M3) drag this down All non-blank lines are now complete descriptions of each item. You can use the filter function to get the complete data descriptions (click on column N then menu item Data- Filter- AutoFilter) Select the non-blank lines option (at the bottom of the drop-down list) Copy and paste to a new worksheet - using 'paste special' - values Repeat for each block of data To make the master list - copy all these concatenated item descriptions in one column on a new worksheet (leave the originals where they are) Use Data- Filter- Advanced filter Use the 'copy to another location' and 'unique records only' Leave criteria range empty Put the destination cell into the box (only the first cell is needed - but it has to be on the same worksheet) and you should have your master list Now you can compare your source, but concatenated, ltem lists to the master using Vlookup I am sure this won't work exactly as I have said, but hopefully it gives you enough to get what you want. Regards