Grabbing specific info from poorly organized worksheets

[tcorbs]

So here's what I want to do. I am trying to create a macro that would compile all the claim numbers (strings) and check amounts from the various source tabs into a list in the "ClaimList" tab. However, I want to add the check amounts for duplicate claim numbers (like a pivot table).

Part of the issue is that the "claim #" column consists of a claim number, then anywhere from 1-4 empty rows, then another claim number, and so on to the end. Fortunately, there are only check amounts in the rows where there is a claim number. Furthermore, the "claim #" column varies from source to source, as does the column for check amount. However, the columns are both always titled, and the column titles are always in row 4.

What is the most efficient way to do this? I set out to do it on my own, but I don't know enough VBA to do it in a reasonable amount of time. Furthermore, it would probably be a very poor algorithm, and I'd rather use something more efficient. I have the tabs set up in excel as follows:

'ClaimList', 'Source_1', 'Source_2', 'Source_3', etc.

Thanks

 

[VoG]

You may be able to use a PT like this http://www.contextures.com/xlPivot08.html

Otherwise please post shots of what you have and what you expect using Excel Jeanie http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[tcorbs]

Yeah, I looked at the multiple ranges thing. I need to be able to rerun this macro immediately after adding another 'Source' tab. And without altering it

For example, if at first I had a single source tab that looked like this:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
4	Payee Name	Full Address	Mail to Address	CK Amount	Overnight mail (y/n)	Insured Name	Decription of Claim	Policy #	Claim #	Policy Eff Date	Policy Exp Date	Date of Loss	If 1099 Reportable list tax id #
5	11/2/2010	*	*	*	*	*	*	*	*	*	*	*	*
6	HOMER	123 EVERGREEN TERR.	SAME	12.00	* NO	HOMER	LEG FRACTURE	234-234	1234-1	9/29/10	9/29/11	10/20/10	* NA
7	*	*	*	*	*	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*	*	*	*	*	*
9	BART	126 EVERGREEN TERR.	SAME	7.00	NO	BART	PNEUMONIA	234-235	1235-0	10/26/09	10/26/10	10/6/10	*NA
10	LISA	127 EVERGREEN TERR.	SAME	4.00	NO	LISA	PNEUMONIA	234-236	1235-1	*	*	*	*
11	*	*	*	*	*	*	*	*	*	*	*	*	*
12	MAGGIE	129 EVERGREEN TERR.	SAME	4.00	NO	MAGGIE	JOINT ILLNESS	235234-54	1236-0	3/18/10	3/18/11	10/6/10	NA
13	*	*	*	*	*	*	*	*	*	*	*	*	*
14	MARGE	131 EVERGREEN TERR.	SAME	2.00	NO	MARGE	SPINAL INJURY	3453-345	1237-0	5/27/10	5/27/11	10/6/10	NA
15	*	*	*	*	*	*	*	*	*	*	*	*	*
16	HOMER	123 EVERGREEN TERR.	SAME	6.00	NO	HOMER	SINUS ILLNESS	3453-345	1235-2	1/20/10	1/20/11	10/13/10	NA
Source_1

Then I would want my output to be this:

Excel Workbook
	A	B	C
3	Claim #	Cum. CK Amount	Change
4	1234-1	12.00	* * * * * * * *12.00
5	1235-0	7.00	* * * * * * * * *7.00
6	1235-1	4.00	* * * * * * * * *4.00
7	1236-0	4.00	* * * * * * * * *4.00
8	1237-0	2.00	* * * * * * * * *2.00
9	1235-2	6.00	* * * * * * * * *6.00
ClaimList

I didn't mention the "Change" column before, but ultimately this will show the change after adding another 'Source' tab. So, then I would add another tab titled Source_2, with the necessary data in (possibly) different columns:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
4	Payee Name	Full Address	Mail to Address	Overnight mail (y/n)	Insured Name	Decription of Claim	Policy #	Claim #	Policy Eff Date	Policy Exp Date	Date of Loss	CK Amount
5	11/2/2010	*	*	*	*	*	*	*	*	*	*	*
6	HOMER	123 EVERGREEN TERR.	SAME	* NO	HOMER	LEG FRACTURE	234-234	1234-1	9/29/10	9/29/11	10/20/10	8.00
7	*	*	*	*	*	*	*	*	*	*	*	*
8	*	*	*	*	*	*	*	*	*	*	*	*
9	BART	126 EVERGREEN TERR.	SAME	NO	BART	PNEUMONIA	234-235	2235-0	10/26/09	10/26/10	10/6/10	6.00
10	LISA	127 EVERGREEN TERR.	SAME	NO	LISA	PNEUMONIA	234-236	2235-1	*	*	*	3.00
11	*	*	*	*	*	*	*	*	*	*	*	*
12	MAGGIE	129 EVERGREEN TERR.	SAME	NO	MAGGIE	JOINT ILLNESS	235234-54	1238-0	3/18/10	3/18/11	10/6/10	5.00
13	*	*	*	*	*	*	*	*	*	*	*	*
14	MARGE	131 EVERGREEN TERR.	SAME	NO	MARGE	SPINAL INJURY	3453-345	1239-0	5/27/10	5/27/11	10/6/10	8.00
15	*	*	*	*	*	*	*	*	*	*	*	*
16	HOMER	123 EVERGREEN TERR.	SAME	NO	HOMER	SINUS ILLNESS	3453-345	1235-2	1/20/10	1/20/11	10/13/10	4.00
Source_2

I would rerun the macro and my output would be this:

Excel Workbook
	A	B	C
3	Claim #	Cum. CK Amount	Change
4	1234-1	20.00	* * * * * * * * *8.00
5	1235-0	7.00	* * * * * * * * * * - *
6	1235-1	4.00	* * * * * * * * * * - *
7	1236-0	4.00	* * * * * * * * * * - *
8	1237-0	2.00	* * * * * * * * * * - *
9	1235-2	10.00	* * * * * * * * *4.00
10	2235-0	6.00	* * * * * * * * *6.00
11	2235-1	3.00	* * * * * * * * *3.00
12	1238-0	5.00	* * * * * * * * *5.00
13	1239-0	8.00	* * * * * * * * *8.00
ClaimList

Thanks

 

[tcorbs]

Day 2 bump. Feel like there should be some relatively short VBA code for this macro. Please let me know if this is not the case, or if my example is not sufficient. Thanks.

 

[tcorbs]

Sorry to bump this again, but it looks like maybe the "New posts" board gets cleared at 9 a.m. I did not know this.