Repeat Data

[Guest]

Hi.. I'm completely stumped on this one..
I have a worksheet and its grouped by State and City and I have multiple
rows of data for each city to top it off.. State and City are in the same
coloumEg
Virgina
Richmond
Capitol 1
Zoo 2
Bars 10
Maryland
Baltimore
Capitol 1
Zoo 3
Bars 3
College 5
I would like the worksheet to be in
Virignia Richmod Capitol 1
Virgina Richmond Zoo 2
Virgina Richmond Bars 10
Maryland Baltimore Capitol 1
and so on..
Can someone help me..
Thx

 

[Guest]

Making the following assumptions:
1 City/State are in Col A
2. Catagories in Col B
3 Qty in Col C
4 No blank rows now exist

Proceed as follows:
A. Insert a new Col A to hold the State
A Find last row in new col C ( dim this LastCatagory)
Loop:
B move up col C a cell at a time, incrementing a counter
If Cell is blank: ( you have arrived at the blank above the Cat group
C Copy the City( oCell.Offset(0,-1) and
paste it to:
Destination:=Range(oCell.Offset(1,-1),oCell.Offset(counter,-1)
D repeat C for the State only copy to new col A
E Delete the original City/State rows
Repeat Loop to top of sheet

Adjust above to fit your scenario