5

I've read elsewhere on Stack Overflow that Excel questions are acceptable here, so please don't get annoyed :) If they should be elsewhere, just let me know...

I'm frustrated, because I'm pretty sure I used to know how to do this.

Imagine the following table:

Frequency       Object
3               A
2               B
4               C

In a third column, I want Excel to write:

A
A  
A
B
B
C
C
C
C

(3 A's because frequency of A = 3)

I'm pretty sure that this can be done by a single formula copied down the third column, but I can't remember how. Any suggestions?

Andrew
  • 1,157
  • 1
  • 20
  • 37
  • #1 Since your question involves a formula, I think it's acceptable here. Any questions more generalized (i.e. how to make a graph) should go to superuser.com. But that's just my opinion. #2 Why do you have openoffice tagged in this question? That's a completely different software. – PowerUser Oct 28 '11 at 15:59
  • Do you have a small finite number of objects? i.e. Is it just A-C or is it more like A-J or some unknown number? – PowerUser Oct 28 '11 at 16:08

3 Answers3

2

I liked the elegance of (1) formula, but it will only work if you dont have repeated objects (data).

This will always work, as long you dont have a numeric, diferent from zero, value in E2 Freq values in E3:E6 and Obj in D3:D6, formula starting in P3

=LOOKUP(ROWS(P$3:P3)-1;SUMIF(INDIRECT("E2:E"&ROW($E$2:$E$6));">0");$D$3:$D$6)

or (and, in this case, you can have anything in E2)

=INDEX($D$3:$D$6;IF(ROWS(L$3:L3)<=$E$3;1;1+MATCH(ROWS(L$3:L3)-1;SUMIF(INDIRECT("E3:E"&ROW($E$3:$E$6));">0"))))

Ctrl+Shift+Enter in P3 and copy down

CR

Shiva Saurabh
  • 1,281
  • 2
  • 25
  • 47
CRondao
  • 1,883
  • 2
  • 12
  • 10
1

Assuming you have a small finite number of objects like in your example, try this:

  1. In a blank sheet, put your example table in the top left. So Cell A2=3, B2="A", A3=2, etc.
  2. In D2, enter "A" (this is just to get the formula started)

  3. In D3, enter this formula: 

    IF(COUNTIF($D$2:D6,$B$2)<$A$2,$B$2,  
IF(COUNTIF($D$2:D6,$B$3)<$A$3,$B$3,  
IF(COUNTIF($D$2:D6,$B$4)<$A$4,$B$4)))

  4. Fill this formula down (i.e. copy&paste) about 10 rows and you'll see everything fill in accordingly.

What does it do, you ask? First, it counts the number of occurrences of "A" in the previous cells of column D and compares it to the frequency. If less, it enters another A. Then that process is repeated for B and C.

PowerUser
  • 11,583
  • 20
  • 64
  • 98
  • Thanks for your reply. I have a potentially large number of finite objects. Has the formula you provided been cut off? There don't seem to be enough closing brackets. – Andrew Oct 31 '11 at 10:54
  • Thanks. I fixed it. Since this formula requires you to enter a separate line for each object, it may not work in your case. – PowerUser Oct 31 '11 at 13:52
1

In the first cell of your desired output column (E1 in this example), enter

=B1

Where B1 is the address of the first object. In the cell below (E2, here), enter

=IF(COUNTIF(E$1:E1,E1)=INDEX($A$1:$A$3,MATCH(E1,$B$1:$B$3,0)),
INDEX($B$1:$B$3,MATCH(E1,$B$1:$B$3,0)+1),
E1)

And fill down as far as you require.

PowerUser
  • 11,583
  • 20
  • 64
  • 98
Excellll
  • 5,609
  • 4
  • 38
  • 55
  • Hello, thanks for your reply. I'm wondering why column E is involved in this formula. I'm assuming that A and B are the columns with data, and C is the column where the third column will be added. – Andrew Oct 31 '11 at 10:50
  • Oh sorry. Column E is where I was entering the formula. I'll edit to add that info. – Excellll Oct 31 '11 at 13:33
  • @Excellll, That is one impressive formula. I think I would have resorted to a macro before trying to figure that one out. Nice! – Stewbob Oct 31 '11 at 16:11