Results 1 to 3 of 3

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

  1. #1

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



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    Attached Files Attached Files

  2. #2
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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.
    Last edited by Simi; 2012-02-11 at 12:11 AM. Reason: added additional question

  3. #3
    Conjurer Simi's Avatar
    Join Date
    Feb 2012
    Location
    Utah, USA
    Posts
    187
    Articles
    0
    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
    Last edited by Simi; 2012-02-12 at 01:18 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •