Consolidate Repetitive Data

**arasan25** · 02-09-2012, 04:41 AM

Hi All

I have a large set of Data that needs to be consolidated. Basically the data contains a large no of repetitive data and I need to consolidate them to analyze it further. Your assistance is much appreciated.

I have attached the spreadsheet with a sample data and the outcome that I would like to obtain. Hope it all makes sense.

Thanks

Tamil

**arlu1201** · 02-09-2012, 04:49 AM

You can achieve the same through SUBTOTALS.

**arasan25** · 02-09-2012, 05:01 AM

Hi Arlette

I tried, but it is not in the format of my "expected outcome" or I am not doing something right?

Thanks

Tamil

**arlu1201** · 02-09-2012, 07:54 AM

As an after thought, i realised that sub totals wouldnt help you. I can provide you a small macro that will do your work.

However, i have a question. I plan on checking 1 row with the next one to see if its same and then sum the values in column F. Which are the fields that i should check for similarity? Is it A to E & G or is just 1 field sufficient?

**arasan25** · 02-09-2012, 09:35 PM

Hi Arlette

It should be A to D & G.

Thanks for your help.

Tamil

**arlu1201** · 02-10-2012, 02:53 AM

Use this code. The output will appear in Sheet 2. I didnt find any headers, you can put them in Sheet1 row2 and in Sheet2 row1.

Please Login or Register  to view this content.

**arasan25** · 02-10-2012, 05:13 AM

Thanks Arlette!

However I am not an expert in excel and my VBA skills certainly sucks!...........would you therefore be kind enough to incorporate this in the spreadsheet? Sorry for being a pain.

Thanks

Tamil

**arlu1201** · 02-10-2012, 05:16 AM

I can guide you along. Press Alt+F11 on your file. It will open a code window. On the left hand side, you will see Microsoft Excel Objects. Right click on it and select Insert-> Module. Copy the above code into it. Go back to your main page. Go to View-> Macros and select the macro that is visible there.
Run it.

**arasan25** · 02-10-2012, 05:48 AM

Hi Arlette

I think the code needs to be tweaked slightly. The similarity check should exclude column G. This should than work (I hope).

Thanks

Tamil

**arlu1201** · 02-10-2012, 05:54 AM

Hey,

You had asked to include column G, so i did. Find the revised code as below

Please Login or Register  to view this content.

**arasan25** · 02-10-2012, 08:01 AM

Hi Arlette,

Yep it was my mistake. Looks like the nos are not there yet. Let me go through the errors in a bit more detail. Once I understand them I'll give you the right picture. Sorry for taking your time on this.

Cheers

Tamil

**arlu1201** · 02-10-2012, 09:21 AM

Hey no, its never a waste of time. What do you mean the nos are not there yet? Are you getting any error?

**arasan25** · 02-13-2012, 01:33 AM

Hi Arlette

Hope you had a good weekend. I was pouring over the spreadsheet to see where the mistake is coming from. Managed to get an answer to the riddle (Problem was, there were too many variables and I have reduced it now). I have attached a revised spreadsheet for your perusal and you can see its a simpler one to the previous sheet. You only need to look at Column B for similarity and it should total Column D accordingly.

Cheers

Tamil

**arlu1201** · 02-13-2012, 05:18 AM

Try this code -

Please Login or Register  to view this content.

Consolidate Repetitive Data

LinkBack

Thread Tools

Rate This Thread

Display

Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Re: Consolidate Repetitive Data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions