Look at this image: EXCEL IMAGE
Or check this "picture to text" example table:
+--------------------+-----+------------+------------+------------+------------+
| Coolie Ghost | | New Frames | Brown N | Red N | Gray N |
+--------------------+-----+------------+------------+------------+------------+
| Frame 4086 | Yes | Frame 2978 | Frame 3938 | Frame 3366 | Frame 3995 |
+--------------------+-----+------------+------------+------------+------------+
| Sprite number = 86 | No | Frame 2979 | Frame 3328 | Frame 3367 | Frame 3996 |
+--------------------+-----+------------+------------+------------+------------+
| Subnumber = 7 | No | Frame 2980 | Frame 2909 | Frame 4088 | Frame 3997 |
+--------------------+-----+------------+------------+------------+------------+
| Frame 4087 | Yes | Frame 2981 | Frame 3939 | Frame 3369 | Frame 4086 |
+--------------------+-----+------------+------------+------------+------------+
| Sprite number = 87 | No | Frame 2982 | Frame 4087 | Frame 3370 | Frame 3998 |
+--------------------+-----+------------+------------+------------+------------+
| Subnumber = 8 | No | Frame 2983 | Frame 3273 | Frame 3371 | Frame 3999 |
+--------------------+-----+------------+------------+------------+------------+
| Frame 4088 | Yes | Frame 2984 | Frame 3940 | Frame 3381 | Frame 4000 |
+--------------------+-----+------------+------------+------------+------------+
Column B range B2:B8 has Yes or No values. It is the result of such function:
=IF(COUNTIF($D$2:$F$8;A2);"Yes";"No"). In other words If column A data has dublicates in D2:F8 range then it outputs Yes.
I need that instead of Yes it would return New Frames values in increasing order from top to bottom: Frame 2978, Frame 2979, Frame 2980 etc. By dragging auto-fill cell the value would be either column New Frames value or empty. By doing this I will know where exactly are dublicate values and what possible non-dublicate value can be used. It will be in the next adjacent cell in B column next to A column.
And also can you tell me how to automatically change actual dublicate cell values in A2:A8 by auto-filling from A2? Instead of Frame 4086, Frame 4087, Frame 4088 in A2 it would be used New Frames from top to bottom: Frame 2978, Frame 2979, Frame 2980 etc.
----2021-01-25 edit:
I would like to add that it is pity that excel doesn't understand such function formula: =IF(COUNTIF($D$2:$F$8;A2);C(1+COUNTIF($B$2:B2; $B$2));"")
Cell C(formula), a formula that would pick up correct cell. It would be either C1 or C2 or C3 etc. Cell name consists of column coordinate letter and row number. Cell coordinate is a reference to cell value. So my C( would be column, but row number should be counted by the formula 1+COUNTIF($B$2:B2; $B$2). But Excel doesn't understand such cell assignment.
Maybe I don't understand how to do it correctly.
The result what I want is this:
+--------------------+--------------+--------------+--------------+--------------+--------------+
| Coolie Ghost | | New Frames | Brown N | Red N | Gray N |
+--------------------+--------------+--------------+--------------+--------------+--------------+
| Frame 4086 | Frame 2978 | | Frame 3938 | Frame 3366 | Frame 3995 |
+--------------------+--------------+--------------+--------------+--------------+--------------+
| Sprite number = 86 | | | Frame 3328 | Frame 3367 | Frame 3996 |
+--------------------+--------------+--------------+--------------+--------------+--------------+
| Subnumber = 7 | | | Frame 2909 | Frame 4088 | Frame 3997 |
+--------------------+--------------+--------------+--------------+--------------+--------------+
| Frame 4087 | Frame 2979 | Frame 2981 | Frame 3939 | Frame 3369 | Frame 4086 |
+--------------------+--------------+--------------+--------------+--------------+--------------+
| Sprite number = 86 | | Frame 2982 | Frame 4087 | Frame 3370 | Frame 3998 |
+--------------------+--------------+--------------+--------------+--------------+--------------+
| Subnumber = 7 | | Frame 2983 | Frame 3273 | Frame 3371 | Frame 3999 |
+--------------------+--------------+--------------+--------------+--------------+--------------+
| Frame 4088 | Frame 2980 | Frame 2984 | Frame 3940 | Frame 3381 | Frame 4000 |
+--------------------+--------------+--------------+--------------+--------------+--------------+
How to achieve it from the first table? By using similar excel functions?
