| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
summarize data from one spreadsheet to other spreadsheet
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 -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=555672 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
summarize data from one spreadsheet to other spreadsheet
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" wrote: 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 -- sa02000 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=555672 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
summarize data from one spreadsheet to other spreadsheet
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 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=555672 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
summarize data from one spreadsheet to other spreadsheet
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 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=555672 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
summarize data from one spreadsheet to other spreadsheet
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 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=555672 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
summarize data from one spreadsheet to other spreadsheet
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 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=555672 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
summarize data from one spreadsheet to other spreadsheet
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 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=555672 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
summarize data from one spreadsheet to other spreadsheet
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 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=555672 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
summarize data from one spreadsheet to other spreadsheet
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 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=555672 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
summarize data from one spreadsheet to other spreadsheet
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 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=555672 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
summarize data from one spreadsheet to other spreadsheet
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 "sa02000" wrote: 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 ------------------------------------------------------------------------ sa02000's Profile: http://www.excelforum.com/member.php...o&userid=27747 View this thread: http://www.excelforum.com/showthread...hreadid=555672 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) | |||
| Matching and Moving Data From One Spreadsheet to Another? | Excel Discussion (Misc queries) | |||
| Inserting a new line in spreadsheet | Excel Discussion (Misc queries) | |||
| Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
| Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
Hybrid Mode
