Excel Grouping and Logic

[83dons]

I have some data in Excel. This is already sorted into the correct order. I require to set the value in a new column to true if it is either a solo record (ie only one distinct ID#) or if there are several entries for that same ID# then set the last one listed to True and the other ones above for that ID# to False. For example:

ID# New Column

001 True
002 True
003 False
003 True
004 False
004 False
004 True
005 True

I need to be able to apply a cell formula to the Excel sheet to auto generate this new column or to run a macro to populate the column. Any help appreciated as this is beyond my skills with Excel!

 

[Fluff]

Hi & welcome to MrExcel
How about
=COUNTIF(A$1:A1,A1)=COUNTIF(A:A,A1)

 

[Joe4]

Say your first value is in cell A2. Place this formula in B2 and copy down:

=IF(A2=A3,"False","True")

 

[83dons]

Hi & welcome to MrExcel
How about
=COUNTIF(A$1:A1,A1)=COUNTIF(A:A,A1)

Hi this works if you use A2 instead of A1 as there is the heading row first. Can you explain the logic of what this formula is doing just so I understand it better?

 

[Fluff]

This part counts how many times A1 occurs anywhere in col A
COUNTIF(A:A,A1)
This part counts how many times the cell value occurs between row 1 & the current row
COUNTIF(A$1:A1,A1)
If one equals the other the formula returns TRUE, otherwise it returns FALSE

 

[Joe4]

Note that if your values in column A are sorted first (like you say that they are), it isn't necessary to count them all and compare to the count so far.
What you simply want is the last instance of each value to be True and the rest to be False. So all you really need to do is check to see if the current value is the same as the value just below it.
If it is, then it is NOT the last value, and should return FALSE.
If is not the same as the value below it, then it is the last of those values and should be TRUE.

That is the very simple logic of the formula I posted.

 

[83dons]

Note that if your values in column A are sorted first (like you say that they are), it isn't necessary to count them all and compare to the count so far.
What you simply want is the last instance of each value to be True and the rest to be False. So all you really need to do is check to see if the current value is the same as the value just below it.
If it is, then it is NOT the last value, and should return FALSE.
If is not the same as the value below it, then it is the last of those values and should be TRUE.

That is the very simple logic of the formula I posted.

Hi yes you are right I just tried it your way and compared it to the previous way it returns the same results. Your way is also much quicker as it is less of a drain on resources I would think. Thanks for replying.

 

[Joe4]

Your way is also much quicker as it is less of a drain on resources I would think. Thanks for replying.

Yes, I would think it would use less resources too.

However, if your data was NOT sorted, that way would work, where mine would not (just something to keep in mind, should that situation ever arise).

 

[83dons]

Ok thanks we have to apply a sort to the spreadsheet first as it is this that places it in the correct order so your option is best for us.