There is list of values (not text, numbers only) in document (for example from B3 to B14). Values may appear in different cells in the list and not always in all of these cells of the list. There may also be empty cells between cells with values.
These values from the list need to be converted into another list where unique values appear. Not unique at all, but unique between cells where the value is break by different values (between cells where different values is appearing). If the value appears again after other values, it is again considered a unique value (for example, if there are values in the list 16, 25, 25, 16, 25, then unique values are 16, 25, 16, 25).
Below these unique values, it should appear how many times these values occur in adjacent cells (ignoring empty cells).
Then it is necessary to create a cells where values are appearing in to mirrored manner.
How to do this in Excel 2019?
There is list of values (not text, numbers only) in document (for example from B3 to B14). Values may appear in different cells in the list and not always in all of these cells of the list. There may also be empty cells between cells with values.
These values from the list need to be converted into another list where unique values appear. Not unique at all, but unique between cells where the value is break by different values (between cells where different values is appearing). If the value appears again after other values, it is again considered a unique value (for example, if there are values in the list 16, 25, 25, 16, 25, then unique values are 16, 25, 16, 25).
Below these unique values, it should appear how many times these values occur in adjacent cells (ignoring empty cells).
Then it is necessary to create a cells where values are appearing in to mirrored manner.
How to do this in Excel 2019?
Output:
Formula's:
| Values | WhenZero | GroupIdentifier | FindingUniqueValues | Counting |
|---|---|---|---|---|
| 200 | =IF(A2=0, 1, 0) | =IF(A2=0, C1+1, 0) | =IF(A2=0, "", IF(COUNTIFS(A$2:A2, A2, C$2:C2, C2)=1, A2, "")) | =IF(D2="", "", COUNTIFS(A$2:A$100, D2, C$2:C$100, C2)) |
| 44 | =IF(A3=0, 1, 0) | =IF(A3=0, C2+1, C2) | =IF(A3=0, "", IF(COUNTIFS(A$2:A3, A3, C$2:C3, C3)=1, A3, "")) | =IF(D3="", "", COUNTIFS(A$2:A$100, D3, C$2:C$100, C3)) |
| 44 | =IF(A4=0, 1, 0) | =IF(A4=0, C3+1, C3) | =IF(A4=0, "", IF(COUNTIFS(A$2:A4, A4, C$2:C4, C4)=1, A4, "")) | =IF(D4="", "", COUNTIFS(A$2:A$100, D4, C$2:C$100, C4)) |
| =IF(A5=0, 1, 0) | =IF(A5=0, C4+1, C4) | =IF(A5=0, "", IF(COUNTIFS(A$2:A5, A5, C$2:C5, C5)=1, A5, "")) | =IF(D5="", "", COUNTIFS(A$2:A$100, D5, C$2:C$100, C5)) | |
| 25 | =IF(A6=0, 1, 0) | =IF(A6=0, C5+1, C5) | =IF(A6=0, "", IF(COUNTIFS(A$2:A6, A6, C$2:C6, C6)=1, A6, "")) | =IF(D6="", "", COUNTIFS(A$2:A$100, D6, C$2:C$100, C6)) |
| 22 | =IF(A7=0, 1, 0) | =IF(A7=0, C6+1, C6) | =IF(A7=0, "", IF(COUNTIFS(A$2:A7, A7, C$2:C7, C7)=1, A7, "")) | =IF(D7="", "", COUNTIFS(A$2:A$100, D7, C$2:C$100, C7)) |
| 25 | =IF(A8=0, 1, 0) | =IF(A8=0, C7+1, C7) | =IF(A8=0, "", IF(COUNTIFS(A$2:A8, A8, C$2:C8, C8)=1, A8, "")) | =IF(D8="", "", COUNTIFS(A$2:A$100, D8, C$2:C$100, C8)) |
| =IF(A9=0, 1, 0) | =IF(A9=0, C8+1, C8) | =IF(A9=0, "", IF(COUNTIFS(A$2:A9, A9, C$2:C9, C9)=1, A9, "")) | =IF(D9="", "", COUNTIFS(A$2:A$100, D9, C$2:C$100, C9)) | |
| 25 | =IF(A10=0, 1, 0) | =IF(A10=0, C9+1, C9) | =IF(A10=0, "", IF(COUNTIFS(A$2:A10, A10, C$2:C10, C10)=1, A10, "")) | =IF(D10="", "", COUNTIFS(A$2:A$100, D10, C$2:C$100, C10)) |
| 16 | =IF(A11=0, 1, 0) | =IF(A11=0, C10+1, C10) | =IF(A11=0, "", IF(COUNTIFS(A$2:A11, A11, C$2:C11, C11)=1, A11, "")) | =IF(D11="", "", COUNTIFS(A$2:A$100, D11, C$2:C$100, C11)) |
(Please note that most formula's are a copy from the row above it)
In Excel 2019, you have some useful functions like Xlookup and Countifs, but no spill formulas, unique or filter.
You would need to add helper columns:
| Values | Blanks filled | Counter |
|---|---|---|
| 200 | 200 | 1 |
| 44 | 44 | 2 |
| 44 | 44 | 2 |
| 44 | 2 | |
| 25 | 25 | 3 |
| 22 | 22 | 4 |
| 25 | 25 | 5 |
| 25 | 5 | |
| 25 | 25 | 5 |
| 16 | 16 | 6 |
Formula in C3 and pulled down:
=IF(B3="",C2,B3)
(assuming there is a number, not a blank in B3 itself)
Formula in d3 and pulled down:
=IF(C2=C3,D2,N(D2)+1)
Then you could generate the required output
| 16 | 25 | 22 | 25 | 44 | 200 | 44 | 25 | 22 | 25 | 16 |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 | 1 | 1 | 2 | 1 | 2 | 1 | 1 | 2 | 1 |
using in I16 and pulled to the left:
=IFERROR(XLOOKUP(COLUMN($J:$J)-COLUMN(),$D$3:$D$12,$C$3:$C$12),"")
in I17 pulled left
=COUNTIFS($B$3:$B$12,I16,$D$3:$D$12,COLUMN($J:$J)-COLUMN())
In J16 and pulled right:
=IFERROR(XLOOKUP(COLUMN()-COLUMN($H:$H),$D$3:$D$12,$C$3:$C$12),"")
and in J17 and pulled right:
=COUNTIFS($B$3:$B$12,J16,$D$3:$D$12,COLUMN()-COLUMN($H:$H))