Home |
Search |
Today's Posts |
#1
|
|||
|
|||
copying matched data, sort of
I have files that contain a header row and then thousands of rows of data.
Column A is a customer identifyer label that is unique. There's only 1 row per customer. EXCEPT for my problem. In some cases, we've repeated the rows to split off have of the data in the other cells to be assigned to 1 salesman amd the other half to another salesman. Now I need to merge them all back together so that... X12345 100 2000 Smith Jones X12345 100 2000 Smith Miller Y12345 300 3000 Johnson Howard becomes.... X12345 200 4000 Smith Miller Y12345 300 3000 Johnson Howard So the amounts are added and the name that appears first is the one that is used. Is there an easy way to do this? Thanks |
#2
|
|||
|
|||
My example is wrong. I'm looking forthe data to look like this.
X12345 200 4000 Smith Jones Y12345 300 3000 Johnson Howard "Donna YaWanna" wrote in message ... I have files that contain a header row and then thousands of rows of data. Column A is a customer identifyer label that is unique. There's only 1 row per customer. EXCEPT for my problem. In some cases, we've repeated the rows to split off have of the data in the other cells to be assigned to 1 salesman amd the other half to another salesman. Now I need to merge them all back together so that... X12345 100 2000 Smith Jones X12345 100 2000 Smith Miller Y12345 300 3000 Johnson Howard becomes.... X12345 200 4000 Smith Miller Y12345 300 3000 Johnson Howard So the amounts are added and the name that appears first is the one that is used. Is there an easy way to do this? Thanks |
#3
|
|||
|
|||
You could use a little macro:
Option Explicit Sub testme() Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Set wks = Worksheets("sheet1") With wks FirstRow = 2 'headers in row 1??? LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then .Cells(iRow - 1, "B").Value _ = .Cells(iRow - 1, "B").Value + .Cells(iRow, "B").Value .Cells(iRow - 1, "C").Value _ = .Cells(iRow - 1, "C").Value + .Cells(iRow, "C").Value .Rows(iRow).Delete End If Next iRow End With End Sub This destroys the data, so test it against a copy of your worksheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Donna YaWanna wrote: I have files that contain a header row and then thousands of rows of data. Column A is a customer identifyer label that is unique. There's only 1 row per customer. EXCEPT for my problem. In some cases, we've repeated the rows to split off have of the data in the other cells to be assigned to 1 salesman amd the other half to another salesman. Now I need to merge them all back together so that... X12345 100 2000 Smith Jones X12345 100 2000 Smith Miller Y12345 300 3000 Johnson Howard becomes.... X12345 200 4000 Smith Miller Y12345 300 3000 Johnson Howard So the amounts are added and the name that appears first is the one that is used. Is there an easy way to do this? Thanks -- Dave Peterson |
#4
|
|||
|
|||
Whoa! I don't even understand the answer. It's not your fault, I'm just a
techno clutz. Is it possible to do this same thing with a formula? Those I can copy and manipulate. "Dave Peterson" wrote in message ... You could use a little macro: Option Explicit Sub testme() Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Set wks = Worksheets("sheet1") With wks FirstRow = 2 'headers in row 1??? LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then .Cells(iRow - 1, "B").Value _ = .Cells(iRow - 1, "B").Value + .Cells(iRow, "B").Value .Cells(iRow - 1, "C").Value _ = .Cells(iRow - 1, "C").Value + .Cells(iRow, "C").Value .Rows(iRow).Delete End If Next iRow End With End Sub This destroys the data, so test it against a copy of your worksheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Donna YaWanna wrote: I have files that contain a header row and then thousands of rows of data. Column A is a customer identifyer label that is unique. There's only 1 row per customer. EXCEPT for my problem. In some cases, we've repeated the rows to split off have of the data in the other cells to be assigned to 1 salesman amd the other half to another salesman. Now I need to merge them all back together so that... X12345 100 2000 Smith Jones X12345 100 2000 Smith Miller Y12345 300 3000 Johnson Howard becomes.... X12345 200 4000 Smith Miller Y12345 300 3000 Johnson Howard So the amounts are added and the name that appears first is the one that is used. Is there an easy way to do this? Thanks -- Dave Peterson |
#5
|
|||
|
|||
I don't think you can do this in one formula.
You could use some builtin techniques and extract the unique list of customer ids. Then use =sumproduct() to get the sum and =vlookup() to return the first and last names of each customer id. But I think that would be more work than learning how to run this macro. Short course: Open your workbook. Hit alt-f11 to get to the VBE (where macros/UDF's live) hit ctrl-R to view the project explorer Find your workbook. should look like: VBAProject (yourfilename.xls) right click on the project name Insert, then Module You should see the code window pop up on the right hand side Paste the code in there. Now go back to excel and save this workbook (so you don't have to do this portion again). Then test it out via: tools|macro|macros... select the macro and click run. === And don't forget to look at David McRitchie's get started site. Donna YaWanna wrote: Whoa! I don't even understand the answer. It's not your fault, I'm just a techno clutz. Is it possible to do this same thing with a formula? Those I can copy and manipulate. "Dave Peterson" wrote in message ... You could use a little macro: Option Explicit Sub testme() Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Set wks = Worksheets("sheet1") With wks FirstRow = 2 'headers in row 1??? LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then .Cells(iRow - 1, "B").Value _ = .Cells(iRow - 1, "B").Value + .Cells(iRow, "B").Value .Cells(iRow - 1, "C").Value _ = .Cells(iRow - 1, "C").Value + .Cells(iRow, "C").Value .Rows(iRow).Delete End If Next iRow End With End Sub This destroys the data, so test it against a copy of your worksheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Donna YaWanna wrote: I have files that contain a header row and then thousands of rows of data. Column A is a customer identifyer label that is unique. There's only 1 row per customer. EXCEPT for my problem. In some cases, we've repeated the rows to split off have of the data in the other cells to be assigned to 1 salesman amd the other half to another salesman. Now I need to merge them all back together so that... X12345 100 2000 Smith Jones X12345 100 2000 Smith Miller Y12345 300 3000 Johnson Howard becomes.... X12345 200 4000 Smith Miller Y12345 300 3000 Johnson Howard So the amounts are added and the name that appears first is the one that is used. Is there an easy way to do this? Thanks -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
I still like the macro idea, but you could insert two new columns (F & G) and
put this in F1 and drag down: =IF(COUNTIF($A$1:A1,A1)1,"DELETETHISLINE",SUMIF($ A$1:$A$100,A1,$B$1:$B$100)) And in G1 (and drag down): =IF(COUNTIF($A$1:A1,A1)1,"DELETETHISLINE",SUMIF($ A$1:$A$100,A1,$C$1:$C$100)) Change those ranges to match your data (I stopped at row 100). Then select columns F & G and edit|copy edit|paste special|Values Apply data|filter|Autofilter to column F. show just the "deletethisline" rows. select those visible cells and delete them. Then copy F over column B and G over column C. And delete F and G. Donna YaWanna wrote: Whoa! I don't even understand the answer. It's not your fault, I'm just a techno clutz. Is it possible to do this same thing with a formula? Those I can copy and manipulate. "Dave Peterson" wrote in message ... You could use a little macro: Option Explicit Sub testme() Dim wks As Worksheet Dim FirstRow As Long Dim LastRow As Long Dim iRow As Long Set wks = Worksheets("sheet1") With wks FirstRow = 2 'headers in row 1??? LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For iRow = LastRow To FirstRow + 1 Step -1 If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value Then .Cells(iRow - 1, "B").Value _ = .Cells(iRow - 1, "B").Value + .Cells(iRow, "B").Value .Cells(iRow - 1, "C").Value _ = .Cells(iRow - 1, "C").Value + .Cells(iRow, "C").Value .Rows(iRow).Delete End If Next iRow End With End Sub This destroys the data, so test it against a copy of your worksheet. If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm Donna YaWanna wrote: I have files that contain a header row and then thousands of rows of data. Column A is a customer identifyer label that is unique. There's only 1 row per customer. EXCEPT for my problem. In some cases, we've repeated the rows to split off have of the data in the other cells to be assigned to 1 salesman amd the other half to another salesman. Now I need to merge them all back together so that... X12345 100 2000 Smith Jones X12345 100 2000 Smith Miller Y12345 300 3000 Johnson Howard becomes.... X12345 200 4000 Smith Miller Y12345 300 3000 Johnson Howard So the amounts are added and the name that appears first is the one that is used. Is there an easy way to do this? Thanks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get a hyperlink (entire row) to data sort alphabetically | Excel Worksheet Functions | |||
sort 2 or more rows of cells linked to one row of data | Excel Worksheet Functions | |||
Removing blank rows in a worksheet | Excel Worksheet Functions | |||
How do I make a formula refer to given data even if I do a sort f. | Excel Discussion (Misc queries) | |||
Data > Sort function amnesia? | Excel Discussion (Misc queries) |