summarize data from one spreadsheet to other spreadsheet

[sa02000]

This question is asked better with the help of an example.....

I have a spreadsheet with data in following format....

ColumnA ColumnB ColumnC ColumnD
date1 1 abc
date1 2 def
date1 4 ghi
date2 2 abc
date2 5 ghi
date2 1 def
date2 6 abc
date3 4 def
date3 7 abc

and I would like to get this data in the following format....

date1 date2 date3 date4
1 abc def
2 def abc
3
4 def
5 ghi
6 abc
7 abc


and then I need to do further processing on this data (which I can
do).

Basically what I am looking for is, in my first spreadsheet identify
each row by a given date (columnA) and corresponding value in columnB
of that row, and now find a cell that is intersection of these ColumnA
and ColumnB values in second spreadhseet and fill in the value of
ColumnC (from spreadsheet 1) into that intersection cell in second
spreadsheet. As my first spreadsheet changes very often I am trying to
get my second spreadsheet modified with those changes and then
everything else I have in second spreadsheet will also change
automatically.

Any help is appriciated.
Thanks,
Jay

 

[JonR]

Here is some code that will do what you want. Type <Alt><F11> to open the
VBA Editor. Select <Insert> - <Module> to open a new module window. Copy
and paste the code below into the module and click the Run button (the blue
arrow).

I'm assuming that your sheets are named "Sheet1", "Sheet2", and "Sheet3",
and that your data is laid out starting on Sheet1, cell A1 like in your
example. If this is not the case, we'll have to make some adjustments to the
code.


Sub Strata()

Worksheets("Sheet1").Activate 'Makes sure you're starting on the right sheet

'declare variables
Dim inRow, inRow2, inCol, stVal, dtDate, inNum, inX, inPasteRow, inPasteCol

'Gather values for row and column headings, and eliminate duplicates

Cells(1, 1).Activate

inRow = ActiveCell.End(xlDown).Row

For inCol = 1 To 2

Range(Cells(1, inCol), Cells(inRow, inCol)).Copy

Worksheets("Sheet3").Activate ' using sheet 3 for a workspace

Cells(1, 1).PasteSpecial

Selection.SortSpecial

inX = 1

'eliminates duplicate values

Do Until Cells(inX, 1).Value = ""

If Cells(inX + 1, 1).Value = Cells(inX, 1).Value Then
Cells(inX + 1, 1).Delete
Else
inX = inX + 1
End If
Loop

inX = 1

'Put row and column headings into Sheet 2

If inCol = 1 Then

Do Until Worksheets("Sheet3").Cells(inX, 1).Value = ""

Worksheets("Sheet2").Cells(1, inX + 1).Value =
Worksheets("sheet3").Cells(inX, 1).Value
inX = inX + 1

Loop

Else
Do Until Worksheets("Sheet3").Cells(inX, 1).Value = ""
Worksheets("Sheet2").Cells(inX + 1, 1).Value =
Worksheets("Sheet3").Cells(inX, 1).Value
inX = inX + 1
Loop
End If

Worksheets("Sheet1").Activate

Next inCol

' Get Row and Column ends to populate data

Worksheets("sheet2").Activate

Cells(1, 2).Activate
inCol = ActiveCell.End(xlToRight).Column
Cells(2, 1).Activate
inRow2 = ActiveCell.End(xlDown).Row

inRow = 1

'Populates data into Sheet 2

Do Until Worksheets("Sheet1").Cells(inRow, 3).Value = ""

dtDate = Worksheets("Sheet1").Cells(inRow, 1).Value
inNum = Worksheets("Sheet1").Cells(inRow, 2).Value
stVal = Worksheets("Sheet1").Cells(inRow, 3).Value

With Range(Cells(1, 2), Cells(1, inCol))
Set c = .Find(dtDate)
inPasteCol = c.Column
End With

With Range(Cells(2, 1), Cells(inRow2, 1))
Set c = .Find(inNum)
inPasteRow = c.Row
End With

'Populate data into cells in Sheet 2

If Cells(inPasteRow, inPasteCol).Value = "" Then

Cells(inPasteRow, inPasteCol).Value = stVal

Else

'this statement will concatenate stVal onto any cells where you have
duplicate date/row entries

Cells(inPasteRow, inPasteCol).Value = Cells(inPasteRow,
inPasteCol).Value & " ," & stVal

End If


inRow = inRow + 1

Loop

End Sub

 

[sa02000]

Jon, Thank for the code. I looked through the code and looks like its
exactly what I was looking for. I have my data scattered into different
columns and data starts at row 8.(one sheet 1). Those columns are not
next to each other either. Those columns are A,C,D. I think I should be
able to adjust the code to look for those columns.

Thanks again.
Jay

 

[sa02000]

Jon, Thank for the code. I looked through the code and looks like its
exactly what I was looking for. I have my data scattered into different
columns and data starts at row 8.(one sheet 1). Those columns are not
next to each other either. Those columns are A,C,D. I think I should be
able to adjust the code to look for those columns.

Thanks again.
Jay

 

[sa02000]

Jon, Thank for the code. I looked through the code and looks like its
exactly what I was looking for. I have my data scattered into different
columns and data starts at row 8.(one sheet 1). Those columns are not
next to each other either. Those columns are A,C,D. I think I should be
able to adjust the code to look for those columns.

Thanks again.
Jay

 

[sa02000]

Jon, Thank for the code. I looked through the code and looks like its
exactly what I was looking for. I have my data scattered into different
columns and data starts at row 8.(one sheet 1). Those columns are not
next to each other either. Those columns are A,C,D. I think I should be
able to adjust the code to look for those columns.

Thanks again.
Jay

 

[sa02000]

Jon, Thank for the code. I looked through the code and looks like its
exactly what I was looking for. I have my data scattered into different
columns and data starts at row 8.(one sheet 1). Those columns are not
next to each other either. Those columns are A,C,D. I think I should be
able to adjust the code to look for those columns.

Thanks again.
Jay

 

[sa02000]

Jon, Thank for the code. I looked through the code and looks like its
exactly what I was looking for. I have my data scattered into different
columns and data starts at row 8.(one sheet 1). Those columns are not
next to each other either. Those columns are A,C,D. I think I should be
able to adjust the code to look for those columns.

Thanks again.
Jay

 

[sa02000]

Jon, Thank for the code. I looked through the code and looks like its
exactly what I was looking for. I have my data scattered into different
columns and data starts at row 8.(one sheet 1). Those columns are not
next to each other either. Those columns are A,C,D. I think I should be
able to adjust the code to look for those columns.

Thanks again.
Jay

 

[sa02000]

Jon, Thank for the code. I looked through the code and looks like its
exactly what I was looking for. I have my data scattered into different
columns and data starts at row 8.(one sheet 1). Those columns are not
next to each other either. Those columns are A,C,D. I think I should be
able to adjust the code to look for those columns.

Thanks again.
Jay

 

[JonR]

Jay,

Glad to help. If you're new to VBA, remember that the cell references are
Cell(row,column), not "column"-"row" like you're used to (ex A1, B12).
That little slip can cause hourse of frustration (experience talking, here).
;-)

Jon