Adding a column with variables for specific entries

**AndyPandy** · 01-31-2013, 06:31 AM

Hi Excel gurus!

Not really sure if my title describes the issue I have in a good way, as I am not sure how to phrase it.

I'll try my best to explain it here.

I have a table which looks like this:

Name 1 IDNumber Name 2 Name 3 Column 5

Tom 20148 John Malmo
Tom 20148 Will Malmo
Bob 20206 Will Malmo
Tom 20206 Will Paris
Bob 20206 Rob Rotterdam
Bob 20207 John Rotterdam
Ray 20207 John Paris
Tom 20208 John Malmo
Ray 20208 Rob London
Ray 20209 Rob Paris
Bob 20209 Will Malmo

Is it possible to have excel go through this list and assign each row a number in column 5 based on the names and the IDNumber? Basically, I would want each entry that is identical in name 1, 2 and 3 to be assigned numbers 1, 2, 3, 4, 5 etc based on their IDNumber. So Tom/John/Malmo with the IDNumber 20148 would get the number 1 in column 5, while the next match (Tom/John/Malmo/20208) would get the number 2 in column 5. For each different match of Name 1,2 and 3, I would want the count in column 5 to start at 1. So Bob/Will/Malmo/20206 would get number 1, Bob/Will/Malmo/20209 number 2 etc.

I hope this made sense. If not, I'll try to clarify.

Thanks!

Andy

PS: I know the columns are a bit cramped together. Not sure how to fix. Hopefully you understand the layout.

**yuvarajgta** · 01-31-2013, 11:06 AM

Hi Andy,

Copy this formula to the column O,first row and drag upto whatever range you want.

Please Login or Register  to view this content.

then in column 5(column E) drag the below formula upto whatever range you want.

Please Login or Register  to view this content.

i think the value you want will be filled in the column 5.

Reply to the post if this really works or not.

Thanks
yuvaraj.

**yuvarajgta** · 01-31-2013, 11:07 AM

Hi Andy,

Copy this formula to the column O,first row and drag upto whatever range you want.

Please Login or Register  to view this content.

then in column 5(column E) drag the below formula upto whatever range you want.

Please Login or Register  to view this content.

i think the value you want will be filled in the column 5.

Reply to the post if this really works or not.

Thanks
yuvaraj.

**AndyPandy** · 02-05-2013, 04:28 AM

Hi Yuvaraj,

Apologies for the late reply. I didn't get a chance to look at this earlier. It works like a charm though

Thanks a lot!

Andy

**AndyPandy** · 02-05-2013, 04:43 AM

One more thing. It seems to be counting them backwards. So if there are three similar entries, it will add them in the column starting with 3 at the top, then 2 and then 1. Is this possible to flip?