Reverse Count Function

riconess22

New Member
Joined
Jan 25, 2013
Messages
30
I'm looking to take a table with consolidated quantities and explode it back into unconsolidated cells. Meaning I have the following table:
Yellow3
Blue5
Red2

<tbody>
</tbody>

I want a formula or macro that then turns or creates a table next to it or below it:

Yellow
Yellow
Yellow
Blue
Blue
Blue
Blue
Blue
Red
Red

<tbody>
</tbody>
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
riconess22,

1. What column is Yellow, Blue, Red in?
2. What is the first cell in that column that contains your information?

3. What column is the 3, 5, 2 in?
4. What is the first cell in that column that contains your information?

5 and 6. What column and first cell do you want the results in?
 
Upvote 0
riconess22,

I assume there are no blank cells in the raw data.

Sample raw data:


Excel 2007
ABCD
1Yellow3
2Blue5
3Red2
4
5
6
7
8
9
10
11
Sheet1


After the macro:


Excel 2007
ABCD
1Yellow3Yellow
2Blue5Yellow
3Red2Yellow
4Blue
5Blue
6Blue
7Blue
8Blue
9Red
10Red
11
Sheet1


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Code:
Option Explicit
Sub ReorgData()
' hiker95, 04/12/2013
' http://www.mrexcel.com/forum/excel-questions/696774-reverse-count-function.html#post3444018
Dim a As Variant, b As Variant
Dim lr As Long, i As Long, ii As Long, iii As Long, n As Long
Columns(4).ClearContents
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A1:B" & lr)
n = Application.Sum(Columns(2))
ReDim b(1 To n, 1 To 1)
For i = 1 To UBound(a, 1)
  For ii = 1 To a(i, 2)
    iii = iii + 1
    b(iii, 1) = a(i, 1)
  Next ii
Next i
Cells(1, 4).Resize(UBound(b, 1), UBound(b, 2)) = b
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.
 
Upvote 0
I assume there are no blank cells in the raw data.

Sample raw data:

Excel 2007
A
B
C
D
1
Yellow
3
2
Blue
5
3
Red
2
4
5
6
7
8
9
10
11

<TBODY>
</TBODY>
Sheet1
After the macro:

Excel 2007
A
B
C
D
1
Yellow
3
Yellow
2
Blue
5
Yellow
3
Red
2
Yellow
4
Blue
5
Blue
6
Blue
7
Blue
8
Blue
9
Red
10
Red
11

<TBODY>
</TBODY>
Sheet1
Code:
Option Explicit
Sub ReorgData()
' hiker95, 04/12/2013
' http://www.mrexcel.com/forum/excel-questions/696774-reverse-count-function.html#post3444018
Dim a As Variant, b As Variant
Dim lr As Long, i As Long, ii As Long, iii As Long, n As Long
Columns(4).ClearContents
lr = Cells(Rows.Count, 1).End(xlUp).Row
a = Range("A1:B" & lr)
n = Application.Sum(Columns(2))
ReDim b(1 To n, 1 To 1)
For i = 1 To UBound(a, 1)
  For ii = 1 To a(i, 2)
    iii = iii + 1
    b(iii, 1) = a(i, 1)
  Next ii
Next i
Cells(1, 4).Resize(UBound(b, 1), UBound(b, 2)) = b
End Sub
What about a one-liner macro (albeit a long one) which is not bothered by blank cells within the data?
Code:
Sub RedistributeText()
  Range("C1:C" & WorksheetFunction.Sum(Range("B1:B" & Cells(Rows.Count, "A").End(xlUp).Row))) = _
     WorksheetFunction.Transpose(Split(Join(WorksheetFunction.Transpose(Evaluate(Replace( _
     "IF(LEN(A1:A#),REPT(A1:A#&""@"",B1:B#),"""")", "#", Cells(Rows.Count, "A").End(xlUp).Row))), ""), "@"))
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top