How to Filter out the data according to the first cell

[Vijayonline2008]

i have an excel sheet in which the city name and corresponding codes are given like this. Datas are in the cell A1:D1, A2:D2 ,A3:D3, A4:D4. There are some thousands of rows in the actual excel sheet


City name code1 Code2 code 3 code4

Mumbai 25 98 154 32

Calcutta 85 94 42 69

Delhi 45 65 75 65


Now i want to arrange the data like this


Mumbai 25

Mumbai 98

Mumbai 154

Mumbai 32

Calcutta 85

Calcutta 94

Calcutta 42

Calcutta 69

 

[SirJB7]

Hi, Vijayonline2008!


First of all your data should be in columns A:E instead of A:D. Assuming it starts at row 1 with titles on sheet 'Hoja1' and that you want the output list in sheet 'Hoja2' with titles in row 1 and data from row 2 in advance, do this in 2nd worksheet:

a) type in A2: =INDICE(Hoja1!A:A;ENTERO((FILA()-2)/4)+2) -----> in english: =INDEX(Hoja1!A:A,INT((ROW()-2)/4)+2)

b) type in B2: =INDICE(Hoja1!B:E;ENTERO((FILA()-2)/4)+2;RESIDUO(FILA()-2;4)+1) -----> in english: =INDEX(Hoja1!B:E,INT((ROW()-2)/4)+2,MOD(ROW()-2,4)+1)

c) copy A2:B2 down as needed


Regards!

 

[Luke M]

Might not be the fastest way, but this will give you the desired output. Assumes that first city and code are in row 2. Output goes into col H and I.

[pre]

Sub ReArrange()
Dim LastRow As Long
Dim i As Long
Dim recordCount As Long
Dim myCity As String
Dim myOffset As Long

LastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Assume we're moving to col H, starting at row 2
recordCount = 2

myOffset = 1
Application.ScreenUpdating = True
For i = 2 To LastRow
myCity = Cells(i, "A")
Do While Cells(i, 1 + myOffset) <> ""
Cells(recordCount, "H") = myCity
Cells(recordCount, "I") = Cells(i, 1 + myOffset).Value
recordCount = recordCount + 1
myOffset = myOffset + 1
Loop
'reset counter
myOffset = 1
Next i

Application.ScreenUpdating = True

End Sub

[/pre]

 

[elhamdkh]

First you can use concatenate to join the city name and codes in for example column F to I, and have it like this:


F G H I

Mumbai25 Mumbai98 Mumbai154 Mumbai32

Calcutta85 Calcutta94 Calcutta42 Calcutta69


and so on...


Then name the whole range as "Data".

now use this formula in a new column:


=OFFSET($F$1;ROUNDDOWN((ROW()-1)/COLUMNS(Data);MOD(ROW()-1;COLUMNS(Data));1;1)