How to rearrange data on excel

[guptaprat]

Hi

I have a sheet with data in the following format

http://i.imgur.com/5T9UN.jpg

and so on for a few hundred lines

Is there any way I can get this in this format:

http://i.imgur.com/tRghT.jpg

Ideally, I would like to have this on a macro, since I have dozens of excel sheets every week I would like to convert.

Any help would be appreciated

Thanks

 

[JackDanIce]

Welcome to the board.

Can't see any examples of what you want

 

[guptaprat]

Are the pictures not loading ?

 

[JackDanIce]

I don't know if the pictures aren't loading, I do know I can't see any examples to show your 'before' and 'after' request

 

[guptaprat]

sorry if it isn't showing up properly. and thanks for your patience as i am still getting a hang of this. I added links, that should work now.

 

[JackDanIce]

The links don't appear to be working for me, though it may be due to work firewall. Suggest using Jeanie Excel (do an internet search for this)

 

[guptaprat]

Was not able to install Jeanne Excel on my work computer, however I am hoping this may just help.

In column A, there is x,y,z, and columns BCDE contains characteristics for each, so below b,c,d are a characteristic of x.

This is the format my excel sheet is currently in
_ A B C D E
1 x a b c d
2 y e f g h
3 z i j k l

I would like it to display in this format
_ A B
1 x a
2 x b
3 x c
4 x d
5 y e
6 y f
7 y g
8 y h
9 z i
10z j
11z k
12z l

Does this help in any way ?

 

[MickG]

Try this:-
Results start "G1"

[COLOR="Navy"]Sub[/COLOR] MG08Jun25
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] AcRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] rw [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
rw = 1
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
   [COLOR="Navy"]Set[/COLOR] AcRng = Dn.Offset(, 2).Resize(, 4)
        Range("G" & rw).Resize(4, 2) = Dn.Resize(, 2).value
          Range("I" & rw).Resize(4) = Application.Transpose(AcRng.value)
             rw = rw + 4
[COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[guptaprat]

Thanks a lot. Works beautifully


I was snooping around the internet and found this as well

Sub Transform()

Dim targetRowNumber As Long
targetRowNumber = Selection.Rows(Selection.Rows.Count).Row + 2

Dim col1 As Variant
Dim cell As Range

Dim sourceRow As Range: For Each sourceRow In Selection.Rows

col1 = sourceRow.Cells(1).Value
For Each cell In sourceRow.Cells

If Not cell.Column = Selection.Column Then
Selection.Worksheet.Cells(targetRowNumber, 1) = col1
Selection.Worksheet.Cells(targetRowNumber, 2) = cell.Value
targetRowNumber = targetRowNumber + 1
End If

Next cell

Next sourceRow

End Sub

 

[guptaprat]

I have a couple of questions, adding to the above macro. I am a novice at vba, so sorry if these are stupid questions. Any Help would be greatly appreciated.

Q1
In the above macro, the target row number is

Selection.Rows(Selection.Rows.Count).Row + 2

Instead of this, I want the data to display on Sheet 2 beginning from A2. How would can I do this ?

Q2
As the data is being displayed on sheet 2, I would like the rows where Column 2 is empty to be deleted. Right now I have a separate macro for that (below), Is there any way I can merge these two macros to run on one command ?

Sub DelRows()
BeginRow = 2
EndRow = ActiveSheet.UsedRange.Rows.Count
ChkCol = 2

For RowCnt = EndRow To BeginRow Step -1
If Cells(RowCnt, ChkCol).Value = "" Then
Cells(RowCnt, ChkCol).EntireRow.Delete
'= True
'Else
' Cells(RowCnt, ChkCol).EntireRow.Hidden = False
End If
Next RowCnt
End Sub

Q3
On the output worksheet, in column D, I have a vlookup function vlookup(C2,'sheet3'!A:B,False), Since there is a lot of data, the existing vlookup function makes the process very slowww. Is there any way I can add the vlookup function to the macro, so that it runs once the above two macros are complete. ?
I would like them to all run on one command rather than having 3 clicks for each macro.

Thanks a lot for the help.