Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

**popcorn7** · 09-26-2018, 11:21 AM

Hi guys,

I am trying to get a macro to run that will aggregate Rows of data based on if 2 columns contain the same value. I know the best way is to use Pivot Tables, but I absolutely can't use a pivot table, this data needs to be aggregated no pivot table as other macros will import it somewhere. Fyi real spreadsheet has 1000's of rows so can't do manually.

I have attached a spreadsheet for your reference.

Basically, the conditions to aggregate the data is:

If Column I & Column W are the same values as another Rows Column I & W, I would like to aggregate those rows to one line, while doing so summing K (the only value that changes among the Rows).

The output should give this result in 3 lines under the headers. (aggregating John's order and Abe's order)

Customer Booked {...} all other data is the same copy paste
JOHN1234 2 {...}
ALAIN1234 5 {...}
ABE1234 6 {...}

Would really appreciate any help I can get, been at this for a couple days. Everything I can find directs me to Pivot tables but I can't do it that way. Thank you!

**sintek** · 09-26-2018, 12:09 PM

Hi popcorn7

JOHN1234 2 {...}
ALAIN1234 5 {...}
ABE1234 6 {...}

This seems incorrect...The red part ...Rows 4 to 7 have same values in F & W
Or must it be I & W

**popcorn7** · 09-26-2018, 12:35 PM

Yes apologies, it is indeed column "I" that I am going by. I will edit post. Any help is greatly appreciated

**sintek** · 09-26-2018, 12:59 PM

Perhaps this...
Results as follows...
JOHN1234 2
ALAIN1234 4
ABE1234 8

Please Login or Register  to view this content.

**popcorn7** · 09-26-2018, 01:34 PM

wow that's a very impressive code, thank you so much!

**sintek** · 09-26-2018, 01:52 PM

Only if it works with your actual dataset...
Thanks.gif

**popcorn7** · 09-26-2018, 03:30 PM

Hey sintek, my actual dataset the Sheet is named "Database" when I change Sheet1 in your code to Database it doesn't work. I also tried writting it Sheets("Database"). I don't see how I can get it modified

can you kindly help me out - this should be straight forward.

**sintek** · 09-26-2018, 03:46 PM

can you kindly help me out

Sure I can ... if I had a file to work with...

**popcorn7** · 09-26-2018, 03:49 PM

Yes apologies. It is back. Really appreciate it sintek

**sintek** · 09-26-2018, 03:54 PM

Code works fine...

Please Login or Register  to view this content.

See result after code ran in upload...

Edit...slight change to code...

Please Login or Register  to view this content.

**popcorn7** · 09-27-2018, 11:27 AM

Hi sintek, I haven't answered you because I have been working on this on my actual data set for a day now and I am still having issues applying it. Your method of doing this is really great, I would like to go by it.

Your code bugs out because when we filter by Client like ALAIN1234, there is in reality many different Order ID's for him, not just one Order ID to aggregate. So I am looking to aggregate each of his orders. I would really appreciate it if you had a look again with this Sample Spreadsheet attached. As it stands it could only be a little edit. I am reaching out to you on last effort, as I said I been at it for a day now and can't get it, my VBA skills aren't your level.

Little Recap what I am trying to do...Basically I am trying to filter by Col I "Client", and again for Col W, aggregating Col K for each Order ID that is the same.

Really appreciate your time

**sintek** · 09-27-2018, 11:33 AM

Upload has no ALAIN1234

**popcorn7** · 09-27-2018, 11:36 AM

I changed the names, the ALAIN1234 was a desensitized name anyways. We can go with the names there now in the upload anyways, it won't change anything. thank you so much for looking at this, I am too still trying to edit

**sintek** · 09-27-2018, 11:59 AM

A bit more complex than we both anticipated lol....Try this...

Please Login or Register  to view this content.

**popcorn7** · 09-27-2018, 12:18 PM

Sintek I can't thank you enough, you are a hero! Really appreciate it

**sintek** · 09-27-2018, 12:27 PM

Is it finally PERFECT...

**popcorn7** · 09-28-2018, 08:26 AM

Hi Sintek,

View my private msg regarding the updates. We are so close. :O Having an issue when there is a Client with 1 order only. Updated Sample Sheet, problem occurs with TROUBLE1234

**sintek** · 09-28-2018, 10:22 AM

Caught me just in time...Away for 2 weeks...

Please Login or Register  to view this content.

**popcorn7** · 09-28-2018, 10:27 AM

Thnak you so much for your help here, I wish you all the best. Have a great time off/travels

All the best to you, can't thank you enough

Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

LinkBack

Thread Tools

Rate This Thread

Display

Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Re: Aggregate Rows of Data if Condition is met (if 2 Columns contains dup values)

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Loop to scan data and aggregate Rows that are the same

Aggregate function with condition

[SOLVED] Aggregate values in columns based on a cell identifier

Aggregate data / remove empty columns

Aggregate data in a range of columns to remove blank cells

Aggregate weekly data by by another condition from daily data

Bookmarks

Bookmarks

Posting Permissions