How to excel?

October 25, 2011 4:37 PM

ExcelFilter: Currently using a clunky nested for loop - is there some way to make this more efficient?

Newbie to Excel/VBA macros, looking for some advice for what should be a pretty simple problem:

I want to print the contents of column A in column C, but each entry in Ax should be printed Bx times.
Column A contains values (assume strings) and Column B contains integers.
So 'A1' should be printed 'B1' times (and occupy 'B1' rows in C), and 'A2' should be printed 'B2' times immediately below.

example:
A|B|C
a|1|a
b|3|b
c|2|b
-|-|b
-|-|c
-|-|c

Right now I'm using a standard nested for loop that pulls ranges A and B and iterates through the two ranges. Range C is filled one line at a time.
The problem is that it seems like it's taking a pretty long time to run.

Is there some clever way of implementing this without using a macro (i.e. using Excel formulas) or a better approach in VBA?
Alternatively, is it possible that I'm making a mistake somewhere (maybe in defining the size of the ranges) that could cause it to run slowly?

posted by hot soup to Computers & Internet (2 answers total)

Best answer: I'm assuming that you have a, 1, a in A1, B1, C1. Here's a way to do this with formulas:

C1: =INDIRECT("A"&E1)
D1: =B1
E1: 1
D2: =IF(D1=1,INDIRECT("B"&E2),D1-1)
E2: =IF(D1=1,E1+1,E1)

Then fill from C1 down, and from D1-E1 down.
posted by grouse at 4:58 PM on October 25, 2011 [1 favorite]

Response by poster: holy cleverness! that's pretty cool, thanks a ton.
the logic in column D is eye-opening - I think my Excel-fu needs some serious work
posted by hot soup at 7:18 PM on October 25, 2011