-3

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.

enter image description here

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?

Improve this question

2 Answers 2

Reset to default
0

Output:

enter image description here

  • 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:

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)

  • Left is doing the transpose, but this is standard Excel.
  • Also left is doing 'mirrored manner'...
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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))
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.