Finding duplicate rows based on values in multiple columns

Hi

I have a data set which has 6 columns (and lots of rows). Every row is different but I want to aggregate them based on 4 fields and then find the average of the numerical column for the results. Sorry if this is unclear but if you are familiar with Access I basically want to Group based on 4 fields and find the average of the 5th field.

My initial approach was to introduce a column which is a combination of the 4 fields I want to group by, simply in the Excel file (=A2&B2&C2&D2) and then find duplicates of that. I have a solution for this in VBA but when importing new data sets in this method is very slow, so I want to be able to do the whole thing in VBA.

The code which I have so far is:

Please Login or Register  to view this content.

The first part finds the unique values (of the column which is a concatenation of the 4 columns) and pastes them into column N. The 2nd part then pastes the relevant averages next to them.

All I need to change is to search for unique combinations of the 4 cells, and then make the same change to the 2nd part. I am a novice with VBA, so apologies in this is not too clear.

Any help would be great.

Thanks

Hi,

Have you used the remove duplicate function?

It can be coded, a little like this?

Please Login or Register  to view this content.

or its is on the ribbon: Data > Data Tools

Originally Posted by foundy

Hi,

Have you used the remove duplicate function?

It can be coded, a little like this? ...

Hmm.
Just to check how well the RemoveDuplicates (vba or ribbon) works, manually type the following 11 data values in column A of a worksheet, and then remove the duplicates

3c
3
6
1c
10
1
4c
5
4c
10
1c

Do you really think the method is useful?

Bit messy but maybe this can help?

Please Login or Register  to view this content.

Not sure how many rows you have, so can't comment on speed.

Thanks for the replies but I'm still stuck.

Foundy I'm not really sure what's going on in the code you posted (sorry I am not good at this...).

Is there no way that the method I originally posted can simply be adapted so that rather than searching for rows with 1 column the same, it searches for rows with 4 (specified) columns the same?

Hi..

Can you attach a sample workbook..

Sample Workbook.xlsm

Here is a sample file. The real file has around 3 times as many rows.

The current VBA is in the file. It is something I found online which works but most likely appears messy. It creates a new table showing the keys and their average prices.

In the file column G is a key, made up by B,C,D & F. The VBA finds the average price (across all locations) for every row with the same key (column G), i.e. it finds a national average for each combination of B, C, D & F.

Once I have the averages I cannot just show them by the key, I need to show them next to the individual columns which make up the key. As you would see if you used a pivot table (or Access query) to calculate these average prices. Using this current method, with the key, the only way I could think to do this was to use vlookups to align the keys with the individual rows (using another key column to match to). This makes the file run very slow so I need to be able to do it all in VBA - finding the averages by matching by combinations of the four columns and then probably creating a new table with 5 columns - for the 4 matched columns and the resulting average price.

I cannot use pivot tables (to sort and copy-paste values etc), as this will make the file too slow.

Apologies for the essay...

Any help would be great

Hi..

Once I have the averages I cannot just show them by the key, I need to show them next to the individual columns which make up the key

I am assuming you mean rows (not columns) there.. as in.. you want the average shown in every row of your main data for each Unique key(B,C,D,F concatenated).

This does that.. i did it in about 20 seconds for your 8737 rows.. not the quickest(have a stretch while its running).. but does the job..

No need for your existing 3 Subs I think..

Workbook attached to show it working..

Please Login or Register  to view this content.

Apo,

Thanks for your help, it wasn't exactly what I needed but I only had to add a bit to get there. I needed it to remove the duplicates (of the 4 column combinations).

I've attached another workbook showing the result. I added the part to remove duplicates after using your method to calculate the averages. I've also added a couple of subs to concatenate columns, rather than having that done in the worksheet, as it will be more efficient going forward. It's probably not the most efficient code but it works quite quickly on my computer so it's all good!

Thanks again

Hi..

Ahh.. I see what you wanted now..

On another note.. having a quick look.. change your ConcatColumns Sub..

From this:

Please Login or Register  to view this content.

To this:

Please Login or Register  to view this content.

Also.. change your Concat2 Sub similarly..