Dynamically align rows with matching records

Microsoft Excel 2003 (full product)
September 18, 2009 at 09:40:39
Specs: Windows XP
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!


See More: Dynamically align rows with matching records

Report •


#1
September 18, 2009 at 10:01:14
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...


Report •

#2
September 18, 2009 at 11:26:15
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


Report •

#3
September 18, 2009 at 11:51:25
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!


Report •

Related Solutions

#4
September 18, 2009 at 12:18:53
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.


Report •

#5
September 18, 2009 at 12:45:56
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


Report •

#6
September 18, 2009 at 14:08:32
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


Report •


Ask Question