Assign value to First and Last row of each group.

Question

This is My Table

+------------------------+

| id | session ID | bal |

+------------------------+

| 0 | 00000002 | 100 |

| 1 | 00000002 | 120 |

| 2 | 00000002 | 140 |

| 3 | 00000001 | 900 |

| 4 | 00000001 | 800 |

| 5 | 00000001 | 500 |

+------------------------+

result Want

+------------------------+

| id | session ID | bal | Assign

+------------------------+

| 0 | 00000002 | 100 | First |

| 1 | 00000002 | 120 | False |

| 2 | 00000002 | 140 | Last |

| 3 | 00000001 | 900 | First |

| 4 | 00000001 | 800 | False |

| 5 | 00000001 | 500 | Last |

+------------------------+

Answer 1

I realize this might be old, but for anyone coming across this who might be interested in a solution using cell functions, this is something you can do only if your table is sorted by column B (session ID) as you have it in your example. There are other solutions if the table is unsorted, but they start to complicated real fast.

Anyways, here are two solutions for a sorted table where session ID is in column B:

=IF(AND(B2=B1,B2=B3),"FALSE",IF(AND(B2=B1,B2<>B3),"Last",IF(AND(B2<>B1,B2=B3),"First","First and Last")))

=IF(COUNTIF(B$1:B1,B2)=0,"First",IF(COUNTIF(B3:B$1000,B2)=0,"Last",FALSE))

Combining IFs with ANDs and ORs allows you to build what is often called a case statement where you can set a value based on your logic. In the first example, the formula simply compares the session ID to the ID above and below it, and chooses which value to have. The second example uses COUNTIF() but is essentially doing the same thing -- checking if the given session ID is in the column above or below it and assigning a value appropriately.

Answer 2

I would set bal as a named range then tell VBA to find that range as my starting position. I would then offset that range to get to the cell that needs to house the first value. Once that range is activated you can land your first value there. I would then activate a cell that is below the dataset (it will be an empty cell) then use function End(xlUp) to find the last row in the dataset that has data in it. I would then use offset again to get the ActiveCell where I want to land my last value, then set that range equal to what your last value needs to be. Let me know if you would like for me to provide further assistance. It may be easier for me to show this process to you once, then I am sure you will have it down from there.

Thanks and I am here if you need me.