Date from Horizontal to Vertical

[christianbiker]

Hi all,

I have a spreadsheet that tracks individuals including names, address & contact information. The information is stored horizontally across the spreadsheet but I am wondering if there is a) a way using formulas to chunk this data together vertically based on each person, or b) a vba code that does the same. I have included an example below with horizonatal data and an example of what I would like it to look like horizontally.

Thanks for any assistance.

Excel Workbook
	B	C	D	E	F	G	H	I	J
2	LAST NAME	FIRST NAME(S)	KID(S) NAMES	STREET (Apt/Unit, No., Street)	CITY	P. CODE	HOME	WORK	CELL
3	King	Jim & Dawn		15 Prospect St.	Toronto	LW5 1W6	555-555-5555	555-555-5555	555-555-5555
4	Morningstar	Tim & Lisa	Johnathon, Bethany, Kimberly, Alexander	21 King St.	St. Catharines	LW5 1W5	555-555-5555	555-555-5555	555-555-5555
5	Rogers	Ed & Janice	William, Faith	18 Main St.	Mississauga	LW5 1W6	555-555-5555
6
7
8	King		Morningstar
9	Jim & Dawn		Tim & Lisa
10	15 Prospect St.		21 King St.
11	Toronto		St. Catharines
12	LW5 1W6		LW5 1W5
13	555-555-5555		555-555-5555
14	555-555-5555		555-555-5555
15	555-555-5555		555-555-5555
DATABASE

Excel 2003

 

[Rasm]

Looks like you just need to select range - copy - then under paste choose transpose

 

[Dryver14]

did you deliberatey leave out the children from Tim and Lisa

 

[christianbiker]

I have tried the transponse function and ran into a problem. The Last Name & First Name remain separate from each other. For purposes elsewhere that needs to say the same. I am thinking of add a hiddine column that joins the two so I think I would prefer VBA code rather than a formula in order to avoind having to unhide and then rehide the column.

The other thing I noticed with Transpose is that it just plops everything on a horizontal plain, but I need to have x number on 1 level, then go to the next level and so on. I need to be able to print this as a directory so going all the way to the right without end is no good. I would also like to have a separating column & row between each person.

And I did leave out the kids...with a hidden column I can bypass that. I could also leave it in I suppose.

Thanks!

 

[Rasm]

Try this in the sheet code - you can make appropriate changes - just click a row and it will process that row

Option Compare Text
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With ActiveSheet.UsedRange
        ColLast = .Columns(.Columns.Count).Column
    End With
    Dim MyValues()
    If ActiveCell.Row >= 3 Then
        Along = 0
        For i = 2 To ColLast
            If ActiveSheet.Cells(ActiveCell.Row, i) <> vbNullString And ActiveSheet.Cells(2, i) <> "KID(S) NAMES" Then
                Along = Along + 1
            End If
        Next i
        ReDim MyValues(Along)
        Along = 0
        For i = 1 To ColLast
            If ActiveSheet.Cells(ActiveCell.Row, i) <> vbNullString And ActiveSheet.Cells(2, i) <> "KID(S) NAMES" Then
                MyValues(Along) = ActiveSheet.Cells(ActiveCell.Row, i)
                Along = Along + 1
            End If
        Next i
        For i = 0 To UBound(MyValues)
            ActiveSheet.Cells(i + 8, ActiveCell.Row - 2) = MyValues(i)
        Next i
    End If
End Sub

 

[Rasm]

replace Activicell.row with Target.row

 

[christianbiker]

Thanks for your help Rasm...this isn't quite what I am looking to do.

What I would like to have is a macro button as the controller of the Sub, not the clicking on a cell. I would also like the data to be transposed to another worksheet as this one sorts it on the same worksheet. Some ranges have blank cells and I would like it to automatically delete them when transposing. I have included below a before and after view in the hope this will help. The data from thefirst example is found on a worksheet called "DATABASE" and the data from the second example is on a worksheet called "DETAILED DIRECTORY".

EXAMPLE 1 - DATA SOURCE

Excel Workbook
	E	F	G	H	I
3	Clark, Jim	32 Wilson St.	Townsend	LW5 1W6
4	King, Chris & Mary	15 Prospect St.	Toronto	LW5 1W6	555-555-5555
5	Knoop, Oliver
6	Morningstar, Rob & Lisa	21 King St.	St. Catharines	LW5 1W5	555-555-5555
7	Snodgrass, Mark & Ruth	18 Main St.	Mississauga	LW5 1W6	555-555-5555
8	Turner, Tim & Lisa	5 Main St.	Brampton	LW5 1W6
DATABASE

Excel 2003

Cell Formulas
Range	Formula
E3	=IF(M3="YES", B3 & ", " & C3,"")
E4	=IF(M4="YES", B4 & ", " & C4,"")
E5	=IF(M5="YES", B5 & ", " & C5,"")
E6	=IF(M6="YES", B6 & ", " & C6,"")
E7	=IF(M7="YES", B7 & ", " & C7,"")
E8	=IF(M8="YES", B8 & ", " & C8,"")

EXAMPLE 2 - FINISHED DATA

Excel Workbook
	A	B	C	D	E
1	Clark, Mark		King, Chad & Julie		Knoop, Oliver
2	32 Wilson St.		15 Prospect St.
3	Townsend		Toronto
4	LW5 1W6		LW5 1W6
5			555-555-5555
6
7	Morningstar, Rob & Lisa		Snodgrass, Dave & Ruth		Turner, Rick & Mary Ann
8	21 King St.		18 Main St.		5 Timberglade Dr.
9	St. Catharines		Mississauga		Brampton
10	LW5 1W5		LW5 1W6		LW5 1W6
11	555-555-5555		555-555-5555
DETAILED DIRECTORY

Excel 2003

Thanks,
Chad

 

[christianbiker]

Anyone have an idea how to do this?