1

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?

Tomasm21
  • 11
  • 2
  • I saw that "Excellll" user is good at such functions and VBA. I expect his answer. – Tomasm21 Jan 22 '21 at 21:43
  • I want that in place of ones would be appropriate frames in ascending order: Frame 2978, Frame 2979, Frame 2980, Frame 2981... etc. Look the image: https://i.ibb.co/dtwv136/image.png – Tomasm21 Jan 23 '21 at 00:09
  • I don’t understand your question.  (0) First of all, [please don’t post images of text.](//unix.meta.stackexchange.com/q/4086/23408)  Post a textual representation of your data, as was done [here](https://superuser.com/q/1289084/150988), [here](https://superuser.com/q/889201/150988), [here](https://superuser.com/q/443967/150988), [here](https://superuser.com/q/684851/150988), [here](https://superuser.com/q/815333/150988) and [here](//superuser.com/q/892744/150988); use the [Format Text as Table](https://senseful.github.io/text-table)   … (Cont’d) – Scott - Слава Україні Jan 23 '21 at 04:29
  • (Cont’d) …  or the [Plain Text Tables generator](http://www.tablesgenerator.com/text_tables) site if you want.  (1) Also, try to post *less* data.  I believe that I’m beginning to understand the question, and I believe that you could illustrate it with less than six rows and eight columns.  (2) Please say simply and clearly what result(s) you want. … … … … … … … … … … … … … … … … … … Please do not respond in comments; [edit] your question to make it clearer and more complete. – Scott - Слава Україні Jan 23 '21 at 04:29

0 Answers0