More Excel help if you please...

[Joe_Fox]

Here is a tricky one for you to have a go at:

I've got the following data

   A     B      C     D
1  JOE   23     24    18
2  KIM   56
3  BOB   19     40
4  LIZ   79     41    42      
5  AMY   88     32

I want to do something so that I get the following from the same data:

   A     B      C     D
1  JOE   23    
2  JOE   24
3  JOE   18
4  KIM   56
5  BOB   19
6  BOB   40
7  LIZ   79      
8  LIZ   41
9  LIZ   42
10 AMY   88
11 AMY   32

So basically moving everything in columns C and D into column B whilst keeping it's related column A data.

Does anyone have any idea how? I'm stumped!

 

[Darth Vodka]

put the filters on

(i take column B is always populated)

copy a & b into a new sheet

filter for non blank column c

copy a & c below the previous into the new sheet

take the filter off, then filter for nonblank column C

copy & paste a & d

(and again for D)

how's that?

(BTW i dont look in this forum very often, if anyone posts any excel/access questions...PM me)

 

[Macky]

Here is a tricky one for you to have a go at:

I've got the following data

   A     B      C     D
1  JOE   23     24    18
2  KIM   56
3  BOB   19     40
4  LIZ   79     41    42      
5  AMY   88     32

I want to do something so that I get the following from the same data:

   A     B      C     D
1  JOE   23    
2  JOE   24
3  JOE   18
4  KIM   56
5  BOB   19
6  BOB   40
7  LIZ   79      
8  LIZ   41
9  LIZ   42
10 AMY   88
11 AMY   32

So basically moving everything in columns C and D into column B whilst keeping it's related column A data.

Does anyone have any idea how? I'm stumped!

looks to me as if Kim, Bob, Liz & Amy are all kicking your ass whatever it is Joe :icon_wink

 

[Darth Vodka]

looks to me as if Kim, Bob, Liz & Amy are all kicking your ass whatever it is Joe :icon_wink

post whore

 

[Joe_Fox]

put the filters on

(i take column B is always populated)

copy a & b into a new sheet

filter for non blank column c

copy a & c below the previous into the new sheet

take the filter off, then filter for nonblank column C

copy & paste a & d

(and again for D)

how's that?

(BTW i dont look in this forum very often, if anyone posts any excel/access questions...PM me)

It's a solution but I the data set I am working with is huge - this technique would take forever and a day. Thanks though.

 

[Darth Vodka]

It's a solution but I the data set I am working with is huge - this technique would take forever and a day. Thanks though.

is there more columns than that?

how many?

hold on...i'll code it up

 

[Darth Vodka]

'---------------------------------------------------------------------------------------
' Procedure : ColumnHeadersToSingleColumn
' DateTime : 14/02/2007 11:34
'
' Purpose : to convert data with column headers to snigle column data
'---------------------------------------------------------------------------------------
'
Sub ColumnHeadersToSingleColumn()
Dim strSourceSheet As String
Dim strTargetSheet As String
Dim intColumns As Integer
Dim intRows As Integer
Dim intTargetRows As Integer

strSourceSheet = "Sheet1"
strTargetSheet = "Sheet2"

Sheets(strTargetSheet).Cells.ClearContents

intRows = 1
intTargetRows = 0

Do
intColumns = 0
Do
Sheets(strTargetSheet).Range("a1").Offset(intTargetRows, 0).Value = Sheets(strSourceSheet).Range("c1").Offset(intRows, -1).Value
Sheets(strTargetSheet).Range("a1").Offset(intTargetRows, 1).Value = Sheets(strSourceSheet).Range("c1").Offset(intRows, intColumns).Value
intTargetRows = intTargetRows + 1
intColumns = intColumns + 1

Loop Until Sheets(strSourceSheet).Range("c1").Offset(intRows, intColumns).Value = ""
intRows = intRows + 1
Loop Until Sheets(strSourceSheet).Range("b1").Offset(intRows, 0).Value = ""

End Sub

 

[Darth Vodka]

works like a dream, but it doesnt format well on here...hold on again

 

[Darth Vodka]

what you're essentially doing is the opposite to what a pivot table does, turning column header data into raw data

happens fairly often as people like to store data in a nice little display table with fancy colours

had to write some code to do the same thing in Access

(4 unanswered posts to me! a PB)

 

[Steven]

Well done darth.

 

[Darth Vodka]

Well done darth.

on talking to myself or coding in VBA ?

 

[Steven]

on talking to myself or coding in VBA ?

Both.