Combine data from matching rows??

[panthercoug]

Any help here would be greatly appreciated, I have the table below.

Year	Make	Model	Sub Model	Engtype	Eng	Fuel	EngCode	PartName	PartNumber	Description
1997	Toyota	Camry	Base	L4	2.2L	CNG	5SNFE	Handle	123	Front Left
1998	Toyota	Camry	Base	L4	2.2L	CNG	5SNFE	Handle	123	Front Left
1999	Toyota	Camry	Base	L4	2.2L	CNG	5SNFE	Handle	123	Front Left
1997	Toyota	Camry	Base	V6	3.0L	Gas	1MZFE	Handle	123	Front Left
1998	Toyota	Camry	Base	V6	3.0L	Gas	1MZFE	Handle	123	Front Left
1999	Toyota	Camry	Base	V6	3.0L	Gas	1MZFE	Handle	123	Front Left
2000	Toyota	Camry	LX	V6	3.0L	Gas	1MZFE	Handle	123	Front Left
2001	Toyota	Camry	LX	V6	3.0L	Gas	1MZFE	Handle	123	Front Left
2002	Toyota	Camry	LX	V6	3.0L	Gas	1MZFE	Handle	123	Front Left

<tbody>
</tbody>

And I would like it to look like this one below in a new sheet.

Begin Year	End Year	Make	Model	Submodel	Engtype	Eng	Fuel	EngCode	PartName	PartNumber	Description
1997	1999	Toyota	Camry	Base	L4	2.2L	CNG	5SNFE	Handle	123	Front Left
1997	1999	Toyota	Camry	Base	V6	3.0L	Gas	1MZFE	Handle	123	Front Left
2000	2002	Toyota	Camry	LX	V6	3.0L	Gas	1MZFE	Handle	123	Front Left

<tbody>
</tbody>

Basically I want to combine the rows if all the cells match exactly except for the year cells, Then in the new table show in the begin year the lowest number from the matching rows and in the end year the highest number of the matching rows.

 

[SydneyGeek]

If you are prepared to have the Begin Year and End Year columns at the end of the row you can use a pivot table.
Drag each of the fields except Year into the Row Labels section. Put Year into the Totals twice, and summarize the first by Min and the second by Max. Re-type the labels to read Begin Year and End Year.
If you are using 2007 or higher, go into the pivot table options (Options tab on the ribbon, Options drop-down on the extreme left). Change the display to Classic (this puts each Row level in its own column)
Now go through the Row Labels columns from right to left, right-clicking on the Totals rows and deselecting the option.
Finally, remove the Grand Total row.

Denis

 

[Marcelo Branco]

A possible solution using Advanced Filter and an array formula

Assuming your data in Sheet1 columns A-K headers in row 1 data beginning in row 2

Put the formula below in M2 (let M1 empty)
=AND(A2<>"",A2=MAX(INDEX(($B$2:$B$100=B2)*($D$2:$D$100=D2)*($E$2:$E$100=E2)*($A$2:$A$100),0)))


On another sheet

go to Data > Advanced Filter

Pick Copy to another location and use these settings

List range: Sheet1!$A$1:$K$100
Criteria range: Sheet1!$M$1:$M$2
Copy to:$B$1

click Ok

you get the columns B to L below (grey area)

A B......................................L

BeginYear	Year	Make	Model	SubModel	Engtype	Eng	Fuel	EngCode	PartName	PartNumber	Description
1997	1999	Toyota	Camry	Base	L4	2.2L	CNG	5SNFE	Handle	123	Front Left
1997	1999	Toyota	Camry	Base	V6	3.0L	Gas	1MZFE	Handle	123	Front Left
2000	2002	Toyota	Camry	LX	V6	3.0L	Gas	1MZFE	Handle	123	Front Left

<tbody>
</tbody>

Put the header "BeginYear" in A1

Array formula in A2
=MIN(IF(Sheet1!$B$2:$B$100=C2,IF(Sheet1!$D$2:$D$100=E2,IF(Sheet1!$E$2:$E$100=F2,IF(Sheet1!$A$2:$A$100<>"",Sheet1!$A$2:$A$100)))))

confirmed with Ctrl+Shift+Enter simultaneously
(hold down both Ctrl and Shift keys and hit Enter)

copy down

Hope this is what you need

M.

 

[panthercoug]

@Sydneygeek
I messed around with the pivot table some (which I am not very familiar with yet lol). But I could not get the result I need.

@Marcelo
I can get to the step of clicking ok and it starts to build on the new sheet correctly but each time it seems to freeze and lockup in excel. I should mention my table does have 700,000 rows. Any suggestions? Because of the amount of data would a macro be a better solution?

 

[Marcelo Branco]

With 700K rows definitively using advanced filter is not the proper solution.

Pivot table as suggested by Denis may work.

Or maybe someone can help with a macro.

M.

 

[Marcelo Branco]

Another possible solution is an SQL query

Select your data and name it, say, MyData

Save and close the file.

Open a new and empty file

Data > From other sources > From Microsoft SQL

1. Pick Excel files

2. Select your previously saved file

3. Use the arrow to transfer MyData to the right panel

4. Next, Next, Next

5. Pick the second option
Show data or edit the query in Microsoft Query

6. Click the button SQL and change to

SELECT Min(MyData.Year) AS 'Begin Year', Max(MyData.Year) AS 'End Year', MyData.Make, MyData.Model, MyData.`Sub Model`, MyData.Engtype, MyData.Eng, MyData.Fuel, MyData.EngCode, MyData.PartName, MyData.PartNumber, MyData.Description
FROM MyData MyData
GROUP BY MyData.Make, MyData.Model, MyData.`Sub Model`, MyData.Engtype, MyData.Eng, MyData.Fuel, MyData.EngCode, MyData.PartName, MyData.PartNumber, MyData.Description

7. Click in the door with an arrow pointing left

8. Ok

Done!

'Begin Year'	'End Year'	Make	Model	Sub Model	Engtype	Eng	Fuel	EngCode	PartName	PartNumber	Description
1997	1999	Toyota	Camry	Base	L4	2.2L	CNG	5SNFE	Handle	123	Front Left
1997	1999	Toyota	Camry	Base	V6	3.0L	Gas	1MZFE	Handle	123	Front Left
2000	2002	Toyota	Camry	LX	V6	3.0L	Gas	1MZFE	Handle	123	Front Left

<tbody>
</tbody>

Hope this helps

M.

 

[panthercoug]

Marcelo,
I used your SQL query in access and it worked perfect. Thank you so much for your help. got me from 1.8M lines down to 600k lol.

 

[Marcelo Branco]

Marcelo,
I used your SQL query in access and it worked perfect. Thank you so much for your help. got me from 1.8M lines down to 600k lol.

Good news!

You are welcome and thanks for the feedback.

M.

 

[Jerry Sullivan]

Hi all,

Marcelo's SQL approach looks like a great fit for your task. In case anyone is interested in a VBA approach for this or a similar task here is some code you could try.

It looks like your data is already sorted. If so, you can comment out the call to the function SortMyAutoData.
Edit the Names in Blue Font to match yours. The code assumes that the Data sheet and Results sheet already exist in your workbook.

Sub VBA_Approach()
    Dim vArr As Variant, vMinYear As Variant
    Dim rTarget As Range
    Dim lCols As Long, lColV As Long, lRowV As Long, lRowR As Long
    Dim bMatch As Boolean

         
    With Workbooks("[COLOR="#0000CD"]AutoData.xlsx[/COLOR]")
        With .Names("[COLOR="#0000CD"]MyData[/COLOR]").RefersToRange
            Call SortMyAutoData(.Cells) '--sort if not already sorted
            lCols = .Columns.Count
            vArr = .Cells
        End With
        '--use separate array for added column so vArr can hold results
        ReDim vMinYear(1 To UBound(vArr, 1), 1 To 1)

            
        lRowR = 1 '--skip header
        For lRowV = 2 To UBound(vArr, 1)
            '--check if next record matches last(except year)
            lColV = 1
            Do
                lColV = lColV + 1
                bMatch = vArr(lRowV, lColV) = vArr(lRowV - 1, lColV)
            Loop While bMatch And lColV < lCols
                  
            If bMatch Then '--update End Year
                vArr(lRowR, 1) = vArr(lRowV, 1)
            Else  '--create new result record
                lRowR = lRowR + 1
                For lColV = 1 To lCols
                    vArr(lRowR, lColV) = vArr(lRowV, lColV)
                Next lColV
                vMinYear(lRowR, 1) = vArr(lRowV, 1)
            End If
        Next lRowV

      
        With .Sheets("[COLOR="#0000CD"]Results[/COLOR]").Range("A1")
            .Parent.Cells.Clear '---Optional: clear previous results
            If lRowR > 0 Then '--write results
                 vMinYear(1, 1) = "Begin Year"
                .Resize(lRowR, 1) = vMinYear
                vArr(1, 1) = "End Year"
                .Cells(1, 2).Resize(lRowR, lCols) = vArr
            End If
       End With
    End With
End Sub

Private Function SortMyAutoData(rData As Range)
    Dim lCol As Long
    With rData.Parent.Sort
        With .SortFields
            .Clear
            For lCol = 2 To 11
                .Add Key:=rData.Cells(1, lCol), SortOn:=xlSortOnValues, _
                    Order:=xlAscending, DataOption:=xlSortNormal
            Next lCol
            .Add Key:=rData.Cells(1), SortOn:=xlSortOnValues, _
                    Order:=xlAscending, DataOption:=xlSortNormal
        End With
        .SetRange rData
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .Apply
    End With
End Function

This worked pretty well for 600K rows of source data on my old laptop which has 8 GB of RAM but raised an Out of Memory error on this line when I tried 700K rows.

vArr = .Cells

If your computer has more memory, you might be able to run the code as is on your dataset, otherwise the code could be modified slightly to break the data set into smaller chunks.