Grouping similar content in rows.

[epactheactor]

Hello!

I'm not even sure if this can be done.

I have several columns with various labels, for the example I am going to use; year, size, weight and height. What I'm trying to do is create a macro that assigns a number to rows to all rows, but puts the same number in those rows where the information is the same as other rows.

So if I had this information

______Year____Size_____Weight____Height
______1989____10______110_______60
______1979____11______130_______90
______1989____10______110_______60
______1990____20______120_______70
______1989____15______111_______90


The macro would do this

______Year____Size_____Weight____Height
______1989____10______110_______60______1
______1979____11______130_______90______2
______1989____10______110_______60______1
______1990____20______120_______70______3
______1989____15______111_______90______4


I know how to group information using IF statements, but that is if I already know what the information might be. Would I need to use some sort of AutoFilter to assign the numbers?

 

[Eric W]

You can do this with a single function. Not pretty or small, but it works. Assuming your columns are A:D, and there is a header row, put 1 in E2, and this function in E3:

=IF(MATCH(A3&B3&C3&D3,$A$2:$A3&$B$2:$B3&$C$2:$C3&$D$2:$D3,0)=ROW(A3)-1,MAX(INDIRECT("$E$2:$E"&MAX(ROW(A3))-1))+1,OFFSET($E$1,MATCH(A3&B3&C3&D3,$A$2:$A3&$B$2:$B3&$C$2:$C3&$D$2:$D3,0),0))

This is an array function, so use Control-Shift-Enter after pasting it in. Then copy this function down the E column as far as you need.

Depending on your numbers, there is a slight chance that you'll get some false matches. If that happens, you'll need to put delimiters between the fields, like A3&"|"&B3&"|"&C3&"|"&D3, everywhere those fields are being concatenated.