VBA to filter on 2 columns and consolidate data into one row

[sncb]

I have data sets that look like this below and pasted from A2 onwards with fixed no of columns but varying no of rows..

and need to process it in such a way that VB would first filter on Col J and then on Col E for unique values (like below)

and from the resulting value remove duplicates and consolidate it into the top row while summing up totals in col L for only the filtered values as shown:

and then continue the process to filter on next value of E (while still filtered on the first value of J)

to get:

and then the filter on next value in Col J and Col E again and so on...such that when i paste the data and run VB, I am able to get something like this:

I know this is a bit complicated but if anyone could help out this would be great for my work purposes. I've tried recording the process as a macro but it records actual values which I cannot use as the data in each such data set that i process has different values.

Tia to anyone who could help out.

Here is the raw data if it might help.

Ccode	Country	CodeShort	Region	Zip	State	DogID	DogType	Owner	Color	Continent	Value
1​	USA	United States	NorthAm	99999​	Texas	4412​	Terrier	Martinez	Green	Americas	5​
1​	USA	United States	NorthAm	99999​	Texas	4412​	Terrier	Hernandez	Green	Americas	10​
1​	USA	United States	NorthAm	99999​	Texas	4916​	GerShep	Lopez	Green	Americas	15​
1​	USA	United States	NorthAm	77777​	Michigan	4916​	GerShep	Gonzalez	Green	Americas	20​
1​	USA	United States	NorthAm	77777​	Michigan	4919​	GerShep	Wilson	Green	Americas	5​
1​	USA	United States	NorthAm	77777​	Michigan	4920​	Collie	Johnson	Green	Americas	10​
1​	USA	United States	NorthAm	77777​	Michigan	4920​	Collie	Williams	Green	Americas	15​
1​	USA	United States	NorthAm	55555​	Georgia	5326​	Doberman	Brown	White	Americas	20​
1​	USA	United States	NorthAm	55555​	Georgia	5326​	Doberman	Jones	White	Americas	5​
1​	USA	United States	NorthAm	55555​	Georgia	7060​	Stray	Garcia	White	Americas	10​
1​	USA	United States	NorthAm	55555​	Georgia	7060​	Stray	Miller	White	Americas	15​
1​	USA	United States	NorthAm	33333​	Montana	8837​	Pom	Davis	White	Americas	20​
1​	USA	United States	NorthAm	33333​	Montana	3249​	Hound	Rodriguez	White	Americas	5​
1​	USA	United States	NorthAm	33333​	Montana	8495​	GoldRet	Smith	White	Americas	10​

 

[sncb]

Hi Peter,

1. Column L
2. When I select the full column it says 'General' on the ribbon but when I select each cell or all the cells with data (only the cells not the column) it says 'Numeric' on the ribbon.

However I'm not able to replicate the error with this below data although with the original data, the error pops up. Ive ensured the column type and cell types as described in #2 above are similar in both the data sets (original and test data). Ive even tried pasting data as values but same error.

Here is some raw data although I simply changed the values to have some 0s in Col L.

Ccode	Country	CodeShort	Region	Zip	State	DogID	DogType	Owner	Color	Continent	Value
1​	USA	United States	NorthAm	99999​	Texas	4412​	Terrier	Martinez	Green	Americas	0​
1​	USA	United States	NorthAm	99999​	Texas	4412​	Terrier	Hernandez	Green	Americas	0​
1​	USA	United States	NorthAm	99999​	Texas	4916​	GerShep	Lopez	Green	Americas	15​
1​	USA	United States	NorthAm	77777​	Michigan	4916​	GerShep	Gonzalez	Green	Americas	0​
1​	USA	United States	NorthAm	77777​	Michigan	4919​	GerShep	Wilson	Green	Americas	5​
1​	USA	United States	NorthAm	77777​	Michigan	4920​	Collie	Johnson	Green	Americas	0​
1​	USA	United States	NorthAm	77777​	Michigan	4920​	Collie	Williams	Green	Americas	15​
1​	USA	United States	NorthAm	55555​	Georgia	5326​	Doberman	Brown	White	Americas	0​
1​	USA	United States	NorthAm	55555​	Georgia	5326​	Doberman	Jones	White	Americas	5​
1​	USA	United States	NorthAm	55555​	Georgia	7060​	Stray	Garcia	White	Americas	10​
1​	USA	United States	NorthAm	55555​	Georgia	7060​	Stray	Miller	White	Americas	0​
1​	USA	United States	NorthAm	33333​	Montana	8837​	Pom	Davis	White	Americas	0​
1​	USA	United States	NorthAm	33333​	Montana	3249​	Hound	Rodriguez	White	Americas	5​
1​	USA	United States	NorthAm	33333​	Montana	8495​	GoldRet	Smith	White	Americas	10​

onary") a = Sheets("Sheet1").Range("A1", Sheets("Sheet1")

 

[Peter_SSs]

Run the code on the problem data again and when it errors and you Debug, hover over the variable "i" in that yellow line and check what value shows in the pop-up.
If it is, say 123, then go to that row in your sheet and look closely at the column L value and see what is in that cell. At this stage I suspect the likely culprit is a text value in that cell or an error value.

 

[sncb]

Run the code on the problem data again and when it errors and you Debug, hover over the variable "i" in that yellow line and check what value shows in the pop-up.
If it is, say 123, then go to that row in your sheet and look closely at the column L value and see what is in that cell. At this stage I suspect the likely culprit is a text value in that cell or an error value.

Got it. Turns out it was a blank cell. I went ahead and filled blank cells with a 0 and it worked great.

Thanks again Peter.

 

[Peter_SSs]

Got it. Turns out it was a blank cell.

A truly blank cell would not cause that error. There must be something in that cell, though it possibly may not be visible.

I went ahead and filled blank cells with a 0 and it worked great.

That would remove whatever was in there and replace it with 0.
That's fine if this is a one-off job or if you are happy to continually do that otherwise we could continue to delve so that eventually the macro would just do the whole job.

 

[Peter_SSs]

Actually, just try adding this blue text and running on the original data again.

If IsNumeric(a(i, 12)) Then d4(s) = d4(s) + a(i, 12)

 

[sncb]

Hi Peter,

Was just writing to you that I can manage with what you provided already but your latest post with the code edit works perfectly.

Thank you once again Peter. Really appreciate your effort towards helping me out here. ?

 

[igold]

@Peter_SSs Thanks for the heads-up on XL2BB.

 

[Peter_SSs]

Hi Peter,

Was just writing to you that I can manage with what you provided already but your latest post with the code edit works perfectly.

Thank you once again Peter. Really appreciate your effort towards helping me out here. ?

You are very welcome. Glad it helped.

@Peter_SSs Thanks for the heads-up on XL2BB.