stack words based on freq

hi excelers,
I have being trying to create a formula that could list words from singles, doubles, triples, numbers... example below:

Book1.xlsx

as you can see from the attachment the formula must locate the freq number, and then make refer to the "word".
hope this is possible and than I explained this clearly, if not please post... thanks stew

See if this does as you need.

Note that the formula in K2 is a CSE array.
It must be confirmed with Ctrl+Shift+Enter, not just Enter, before dragging accross then down.

I can't tell from your file, what's your given data and what's your results? Is A1:B3 given and you want to fill in the values in A6:B12? Or is it the opposite? Or something else?

Assuming there's the possiblity of ties, I first created a helper column in C11 (in my slightly modified sheet)
=IF(ISNUMBER(A11),A11&"_"&COUNTIF($A$11:A11,A11),"")
This gives each value a unique ID

Then in A2
=IFERROR(LARGE($A$11:$A$17,ROW(A1)),"") copied down

Then in B2 copied down
=IFERROR(INDEX($B$11:$B$17, MATCH(A2&"_"&COUNTIF($A$2:A2,A2),$C$11:$C$17,0)),"")
See attachment
Questions?

You'll need some form of VBA to do this.

Excel 2011 is a Mac version, right? Does it support VBA?

Hello,

Not sure if I understand your problem, but you can try the Array formula in this sample file
Show words.xlsx
If you change the cell before any of the word in the list (From A8 to A13) to 1 or -, the list will automatically update.

I think they want the results to appear in a single cell.

Here's a tip you might find useful...

Using the ROW() function makes the formula specific to the row(s) it's entered on and makes it vulnerable to new row insertions before the referenced range.

Insert a new row 1 and see what results your formula generates.

You can make the formula more robust by using the ROWS(...) function instead and, it doesn't make the formula overly complex or inefficient.

Here's how I would write that formula:

=IF(ROWS(A$1:A1)>COUNTIF(A$8:A$13,1),"",INDEX(B:B,SMALL(IF(A$8:A$13=1,ROW(A$8:A$13)),ROWS(A$1:A1))))

Insert a new row 1 and see the difference.

Is this what you are looking for?
Book1.xlsx
Just put 1 somewhere or delete 1 somewhere, and you will see the list changing on itself

Originally Posted by Stews

I have attached the sheet below.

OK, you have the correct formula entered in cell A1 but you have to enter it as an array formula.

Array formulas are entered differently than a regular formula.
After you type in a regular formula you hit the ENTER key.
With an array formula you *must* use a combination of keys.
Those keys are the CTRL key, the SHIFT key and the ENTER key.
That is, hold down both the CTRL key and the SHIFT key then
hit the ENTER key.

When done properly Excel will enclose the formula in squiggly
brackets { }. You can't just type these brackets in, you
*must* use the key combo to produce them. Also, anytime
you edit an array formula it *must* be re-entered as an
array using the key combo.

So, array enter the formula then drag copy down until you get blanks.

Originally Posted by Stews

is it possible to also add 2 or 3 or 4 etc in the formula.

Not sure what you mean. Do you mean you want to extract the data that corresponds to 1 OR 2 OR 3 OR 4...

1...A
5...X
4...T
7...R
2...W

Result would be:

A
T
W

Is that what you want?

I'm not sure what you want. Isnt' the number in Column A refer to the amount of each word. Can you upload an example EXCEL file with what you have and what you want (Go Advanced> Manage Attachments)?

Hmmm, the file you uploaded doesn't have any of the numbers that you had in post 1 and doesn't help at all with this in Post 5

is it possible to get it to list doubles or 3s or 4s! (each columa)

This Array equation in B2 stacks all items below, even if they have no number next to them.

=IFERROR(INDEX($B$11:$B$17, MATCH(A2&"_"&COUNTIF($A$2:A2,A2),$C$11:$C$17,0)),IFERROR(INDEX($B$11:$B$17,SMALL(IF($A$11:$A$17="",ROW($A$11:$A$17)-ROW($A$11)+1,10000),ROW(A1)-COUNT($A$11:$A$17))),""))
See attached.