macro to compare data on worksheet 1 and put the information on worksheet 2

[twinny]

Dear All,
Kindly assist with a macro that will compare data on worksheet 1 and consolidate the information on worksheet 2.


Please find attached sample data.

Kindly view the explanation below.

The first sheet contains raw data ; with 4 Columns


The first column (Shop Number)

The second column (Brand Name)

The third column (Type)- different types of brand items

The fourth column (Amount)- amount sold per item type.

The second sheet is expected to consolidate the first sheet with the following columns

Shop Number

Brand name

Count

Amount

The first column will contain a unique representation of the shop number for each brand name.

Count will contain the number of occurrence of the brand name as seen in first sheet (raw data)

Amount will compute the calculation of the values representation of the data as seen in the raw sheet.

Any assistance will be highly appreciated.

Thank you.

Twinny

[Simi]

In your sample file you have Shop Number 101 and Brand name ABC, will ABC ever appear with a different shop number like 102 ABC? Additionally will you ever have a blank shop number, brand name?


I am trying to accomplish very much the same thing with the exception of putting my data in a different workbook.
I will be finishing up my code this weekend, and will re-post Monday, hopefully with your feedback.

[Simi]

When I first read your post I was over-thinking it a tad. The below code will work to consolidate the data you were trying to get.
Please note you need to sort sheet(1) by column A and then column B to work, if the data is not sorted you will need extra code and loops to compare every line every time you have a new item. You can use the VBA to sort the data for you before hand. Any way hope this works for you.


Sub a_Copy_data()


'declare variables
Dim iSourceRowCurrent
Dim iDestinationRowCurrent
'initialize variables
iSourceRowCurrent = 2 'first row is a header
iDestinationRowCurrent = 2 'first row is a header


'this code will only work properly if the data is sorted before hand.


'first simply copy the first line of data to sheet2
If IsEmpty(Sheets(1).Cells(iSourceRowCurrent, 1)) = False Then
Sheets(2).Cells(iDestinationRowCurrent, 1) = Sheets(1).Cells(iSourceRowCurrent, 1)
Sheets(2).Cells(iDestinationRowCurrent, 2) = Sheets(1).Cells(iSourceRowCurrent, 2)
Sheets(2).Cells(iDestinationRowCurrent, 3) = Sheets(1).Cells(iSourceRowCurrent, 3)
Sheets(2).Cells(iDestinationRowCurrent, 4) = Sheets(1).Cells(iSourceRowCurrent, 4)
iSourceRowCurrent = iSourceRowCurrent + 1
Else
Exit Sub
End If


'next compare data to roll up and consolidate.
Do While IsEmpty(Sheets(1).Cells(iSourceRowCurrent, 1)) = False
If Sheets(2).Cells(iDestinationRowCurrent, 1) = Sheets(1).Cells(iSourceRowCurrent, 1) And Sheets(2).Cells(iDestinationRowCurrent, 2) = Sheets(1).Cells(iSourceRowCurrent, 2) Then
'if they are the same shop# and Brand name, increment the count, and add the amounts
Sheets(2).Cells(iDestinationRowCurrent, 3) = Sheets(2).Cells(iDestinationRowCurrent, 3) + 1
Sheets(2).Cells(iDestinationRowCurrent, 4) = Sheets(2).Cells(iDestinationRowCurrent, 4) + Sheets(1).Cells(iSourceRowCurrent, 4)
'increment the row on sheet 1 to continue the check
iSourceRowCurrent = iSourceRowCurrent + 1
Else
'increment the row on sheet 2 to add new data
iDestinationRowCurrent = iDestinationRowCurrent + 1
'copy the data to the next row down on sheet 2
Sheets(2).Cells(iDestinationRowCurrent, 1) = Sheets(1).Cells(iSourceRowCurrent, 1)
Sheets(2).Cells(iDestinationRowCurrent, 2) = Sheets(1).Cells(iSourceRowCurrent, 2)
Sheets(2).Cells(iDestinationRowCurrent, 3) = Sheets(1).Cells(iSourceRowCurrent, 3)
Sheets(2).Cells(iDestinationRowCurrent, 4) = Sheets(1).Cells(iSourceRowCurrent, 4)
'increment the row on sheet 1 to continue the check.
iSourceRowCurrent = iSourceRowCurrent + 1
End If
Loop


End Sub