I have an excel chart with thousands of Samples in one column. The sample appears in a row three times matching up to a different column called Target which is normal. I am looking to seeing if the sample duplicates 4,5 or 6 times and add a "-r2" to the end of the Sample name, and if the Sample duplicates 7,8, or 9 times and add a "-r3" to the end of the Sample name. I am trying to find a formula that ignores the first three times a Sample is counted and only count duplicates from ranges 4-6 as "-r2" and 7-9 as "-r3".
Below is an example of what I would want my new column (called "New Sample") to look like. It's worth noting that not every sample is duplicated more than 3 times, sometimes 6 or 9.
| Sample | New Sample | Target |
|---|---|---|
| 1 | 1 | top |
| 1 | 1 | middle |
| 1 | 1 | bottom |
| 1 | 1-r2 | top |
| 1 | 1-r2 | middle |
| 1 | 1-r2 | bottom |
| 1 | 1-r3 | top |
| 1 | 1-r3 | middle |
| 1 | 1-r3 | bottom |
| 2 | 2 | top |
| 2 | 2 | middle |
| 2 | 2 | bottom |
| 3 | 3 | top |
| 3 | 3 | middle |
| 3 | 3 | bottom |
| 3 | 3-r2 | top |
| 3 | 3-r2 | middle |
| 3 | 3-r2 | bottom |
