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?

• excel
• extract

2 Answers 2

Output:

• In column B, create a helper column to mark when a zero appears:
• Use a running total in column C to create a "group identifier"
• In column D, check if a value appears earlier in the same group
• In column E, count occurrences of each unique number in its group

Formula's:

(Please note that most formula's are a copy from the row above it)

• Left is doing the transpose, but this is standard Excel.
• Also left is doing 'mirrored manner'...

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:

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

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))