Arrays

[sam.burley]

I have a set of values on one work sheet. On the second worksheet i
have a number of the same values with dates next to them. I need to
have the values from the first worksheet outputed horixontally on the
second worksheet.

That is:

worksheet one

abs 1/1/10
abs 1/1/11
abb 1/14/05
ab3 1/16/05
ab3 1/17/17
try 1/1/04

worksheet two

abs
abb
ab3

what i need
A B C
abs 1/1/10 1/1/11
abb 1/14/05
ab3 1/16/05 1/17/17

There are a lot of values (4000 on sheet 1 with 267 jobs (abs etc.) on
sheet 2). any help would be greatly appreciated.

thanks,
sam

 

[Dave Peterson]

Your data is already sorted????

If yes, how about a little macro:

Option Explicit
Sub testme()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim iRow As Long
Dim oRow As Long
Dim oCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim PrevKey As Variant

Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add

PrevKey = ""
With curWks
FirstRow = 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

oRow = 0
oCol = 1
For iRow = FirstRow To LastRow
If .Cells(iRow, 1).Value = PrevKey Then
'keep adding to the right
newWks.Cells(oRow, oCol).Value = .Cells(iRow, 2).Value
oCol = oCol + 1
Else
'new key, go to next row
PrevKey = .Cells(iRow, 1).Value
oRow = oRow + 1
newWks.Cells(oRow, 1).Value = .Cells(iRow, 1).Value
newWks.Cells(oRow, 2).Value = .Cells(iRow, 2).Value
oCol = 3
End If
Next iRow
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[sam.burley]

Thank you, that worked well. And now, when i get a chance, i can figure
out why...

thanks again,

Sam

 

[Dave Peterson]

Magic.

(Glad it worked for you.)

Thank you, that worked well. And now, when i get a chance, i can figure
out why...

thanks again,

Sam