Return a value as output based on first 5 columns

Hi,

I have a spreadsheet attached that contains information in the first 5 columns as follows:

Date
Location
R
Type
R T

I need to return a number for each unique value, I have inserted a column with values in the attached in Col F as what I am trying to achieve.

Essentially, if the date, location, R and R T are the same (type does not matter at this stage), then it should return a number starting from 1 and increasing by 1 each time.

My sheet has many rows, with multiple dates, locations, R, Types and R T's, but attached is only a sample.

Hopefully this has explained what I am looking for, but if not please let me know.

I tried using SUMPRODUCT but could not get the desired result.

Typed on F2 and copied down as necessary

=IF(COUNTIF(E$2:E2,E2)=1,MAX(F$1:F1)+1,VLOOKUP(E2,E1:F$2,2,0))

thanks azumi, but when I add a new row with a different location and same R T, it goes back to 1. I need this number to continue to increase so should return 5. I have shown in the attached by adding data to row 71.

Originally Posted by benishiryo

hi there. this could slow down your workbook a fair bit. try it in cell F2

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Thanks benishiryo, yes it works but unfortunately when I copy it down 30,000 rows, it crashes Excel on my machine, so you are right in saying it would slow down my workbook

Perhaps, put this on F2 and copied down, i use column C and D as guidelines

=IF(COUNTIFS(C$2:C2,C2,E$2:E2,E2)=1,MAX(F$1:F1)+1,VLOOKUP(E1,E1:F1,2,0))

Thanks again azumi, but this does not provide the correct result in my master spreadsheet with 30,000+ rows. I think that when the cat is the same as the cat above (even though it is a different date and location, it returns the same as the previous value

thanks FlameRetired. Your formula works, and here is a more comprehensive workbook.

However I have the same problem when it calculates (it crashes Excel on my PC) ??

Hi FlameRetired

yes, I closed Excel and started again and all fine now

One further question, if I have a label in row 1 as a header, and my formula starts in row 2, how can I adjust accordingly (as currently returns a #VALUE! error).

perfect, thanks FlameRetired

Now, if I wanted to count each of these instances in a pivot table (or otherwise), how can I achieve this? I have shown in the attached example, but essentially if I wanted to count all of the Cat 1's in RT, there would actually only be one of these (as the date, location, and R T are the same) and so on.

brilliant, thank you!

Cat 112 is definitely 2, as it has 2 instances of R. I have included the column with R and it now works

Please Login or Register  to view this content.