How to identify triplet rows among pairs

[sotplugy]

Hello Excelforum community,
How do I identify triplet rows (having a unique identifier 3x) among pairs, and work out some fields on the right?

The unique identifier here is my BB (b1 was repeated 3x so it's a triplet; b2 is repeated twice so it's a pair)
For each triplet, I want to identify the earliest and latest timestamp (1 and 3 in this example) and leave the data on the right as it is, but I want to clear out the columns for any row in between (b1c2 here)
And just ignore the pairs

The problem is I have many rows and rows, leaving the rows of triplets/pairs far apart from each other. How could I sort them out using some sort of macro?

rowstime.PNG

Appreciate any help please.

*note: Rows with source C have negative values

[mgs73]

Have you considered formulas? In column CBP, put a formula that checks the previous row's and next row's BB column, and if both are equal to the current row then make cell blank, otherwise concatenate BB and CC columns. Then for the CB and AA columns, put a formula that will set cell to blank if CBP column is blank, otherwise set to 'Full' or 0 as needed.

e.g.

Please Login or Register  to view this content.

[sotplugy]

I would have to code it in vba to work alongside with a longer code that populates CB, CBP and AA... would that be possible? Also previous row and next row won’t cut it because they are actually far apart in the dataset

[mgs73]

Can you upload the actual spreadsheet? I'm struggling to visualise what it is you are trying to do.

Are you saying that in column BB, there are thousands of values, all of which are repeated once or twice, but never more? Are some values not repeated?

[sotplugy]

This is closest to the actual data. Yes, in BB, b1 could appear once, or twice, or thrice, or many times. But there are ways to identify these rows we are interested, resulting in either pairs or triplets only. The actual data is more complicated but I’ve simplified it in this example.

[mgs73]

I'm still not sure I understand. Are you only interested in items that are in the list three times? Singles, duplicates, and anything else gets left alone?

If all you want to do is identify triplicates and clear some cells, then try this:

Please Login or Register  to view this content.