Sort numbers

[mihulea]

I have a sequence of numbers of which some of them repeat , so I used the countif function to list how many times each number repeats if exists in the sequence. Now I want to group them(in a table) so the numbers that never occur in the sequence will be listed under 0, numbers that come up once under 1 and so on. i.e numbers : 1,2,3,4,5,6,7,8,9 ; sequence 2,3,5,3,3,7,8,6,7,5,7,5,8. So we have 1 and 9 should be listed under 0; 2,6 under 1; 8 under 2 and 3,5,7 under 3.

[ashishmehra2010]

Hi,

Welcome to the forum!

I suggest you to post a sample file with the output you want.

This practice makes life easier for the other users to understand the question better.

Regards,
AM

[mihulea]

Hi,

Hope this helps.

[oeldere]

With 2 helpcolumns and after that index / match.

See the attached file.

The result is in the green cells.

[Shreyans Nahata]

Make a pivot of Columns Number and Repeat Times and the put both under Column Labels in Pivot Table Field List

[mihulea]

Thank you oeldere!

Wow !Nice one. I have to digest this for a little while to understand how it works, but yes this is it.

[Shreyans Nahata]

It ain't exactly how you wanted it, but this is the easiest & quickest thing for any analysis...

[oeldere]

P3=countif($N$3:$N3,$N3) and dragged down
Q3=N3&P3 and dragged down

b21=iferror(INDEX($L$4:$Q$11,match(B$14&row()-20,$Q$4:$Q$11,0),1),"")

dragged down and to the right.

Explaination:

b21 iferror(formula,"")
if the formula is wrong => return an empty cell.

b21 row()-20 returns => row 21 -20 = 1
b22 with the same formula returns => row 22 - 20 = 2

b14 = 0 so this part B$14&row()-20 returns => 01

The index / match formula looks for the value 01 (see the explaination above) in column Q and returns the value on the same row in column L (in which is 9).

I hope I explained the trickey parts well enough.

If not just ask.

[mihulea]

With 2 helpcolumns and after that index / match.

See the attached file.

The result is in the green cells.

=IFERROR(INDEX($L$4:$Q$11,MATCH(B$14&ROW()-20,$Q$4:$Q$11,0),1),"")

In the above why did you start from L4 and Q4 ?

[oeldere]

@mihulea

That is a very good catch.

The earlier formula is wrong. it has to be like below.

See in the changed attached file.

In the earlier file it did not go wrong (excedently), since there where no values with 1 hit.

Sorry for the unconviency.

The formula should be =IFERROR(INDEX($L$3:$Q$11,MATCH(B$14&ROW()-20,$Q$3:$Q$11,0),1),"")

[mihulea]

@mihulea

That is a very good catch.

The earlier formula is wrong. it has to be like below.

See in the changed attached file.

In the earlier file it did not go wrong (excedently), since there where no values with 1 hit.

Sorry for the unconviency.

The formula should be =IFERROR(INDEX($L$3:$Q$11,MATCH(B$14&ROW()-20,$Q$3:$Q$11,0),1),"")

Im doing something wrong. In blue is what I did, but I get no values????

[mihulea]

@mihulea

That is a very good catch.

The earlier formula is wrong. it has to be like below.

See in the changed attached file.

In the earlier file it did not go wrong (excedently), since there where no values with 1 hit.

Sorry for the unconviency.

The formula should be =IFERROR(INDEX($L$3:$Q$11,MATCH(B$14&ROW()-20,$Q$3:$Q$11,0),1),"")

Got it!!! Dont mind my previous message.I used 20 for row instead of 28!

[oeldere]

b29 =IFERROR(INDEX($L$3:$Q$11,MATCH(B$14&ROW()-28,$Q$3:$Q$11,0),1),"")

since you start in row 29 you have to abstract 29 - 28 (to get the wanted result 1).

your formula gave the result 29 - 20 = 9.

Hope I explained well enough.

[mihulea]

b29 =IFERROR(INDEX($L$3:$Q$11,MATCH(B$14&ROW()-28,$Q$3:$Q$11,0),1),"")

since you start in row 29 you have to abstract 29 - 28 (to get the wanted result 1).

your formula gave the result 29 - 20 = 9.

Hope I explained well enough.

You are a genius. I cant say anymore!

[oeldere]

Thanks for the nice compliment.

Hope you understand where it get wrong, so you can amend the formula for future use.