Numbering matching instances - but not a vanilla ranking

df9864

Board Regular
Joined
Sep 28, 2004
Messages
108
Hi - I'd really appreciate some help with this as I've hit the wall.
I need to identify matching items and number them (1 to x) by category. It's much easier to show you what I mean with a table below - please excuse the food based data points - it's for example purposes only :)
I don't want to do any sorting and the fields are in an Excel table.

Category1 Category2 Desired result
Fruit apple Fruit1
Fruit banana Fruit1
Fruit apple Fruit2
Fruit apple Fruit3
Fruit pear Fruit1
Fruit pear Fruit2
Fruit pear Fruit3
Veg brocoli Veg1
Veg cabbage Veg1
Veg cabbage Veg2
Veg cabbage Veg3
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Actually I just figured it out lol. it's a simple countif statement. Concatenating Cat1 (col A) & Cat2 (col B) into column C, it would be the following
= A2 & COUNTIF($C$2:C2,C2)

I'll give myself a pat on the back!!
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top