Matching data in Excel [Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,

I hope someone can help with an easy way to do this!

I have a list of customer numbers in column A and I need to create a table showing month by month if each customer has paid or not.

I have result lists in excel for each month and I need to merge them together so I have one full report showing:

Customer Number-----Jan----Feb-----March---

And then under each month, accepted or declined.

Problem is that each month the amount of customers is different, some cancel and are removed so I cant just copy them into one sheet as they won't correspond properly.

Can anyone help??

Thanks





4 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site , http://wikisend.com/ ,https://accounts.google.com/ServiceLogin?passive=1209600&continue=https://docs.google.com/&followup=https://docs.google.com/&emr=1 http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too

http://wikisend.com/download/503680/Excelbeginner_Example.xls

I thought I just posted this..weird.

Anyway, I need to match the three sheets and compile a master payment result list, including all months.

The variable is the customer number (which can be present one month but not the rest) and the payment result (Y or N).

The point of this is so that I can see the individual customers payments, ie did they pay in April, May and June or did one or more of those months fail.

Thanks so far!
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
Try it

Sub SummarizeData() 
Dim sMonths As Variant 
Dim sMonth As Variant 
Dim sSummarySheet As String 
Dim lSummaryRows As Long 
Dim lMaxRows As Long 
Dim iMonth As Integer 

    sMonths = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December") 
     
    sSummarySheet = "Consolidated" 
     
    On Error Resume Next 
        Sheets(sSummarySheet).Delete 
    On Error GoTo 0 
     
    Sheets.Add 
    ActiveSheet.Name = sSummarySheet 
    Cells(1, 1) = "Customer" 
    Cells(1, 2) = "Customer" 
    lSummaryRows = 2 
     
    For Each sMonth In sMonths 
         
        Sheets(sSummarySheet).Cells(1, Columns.Count).End(xlToLeft).Offset(0, 1) = sMonth 
         
        On Error Resume Next 
            Sheets(sMonth).Select 
        On Error GoTo 0 
         
        If (ActiveSheet.Name <> sMonth) Then GoTo Next_sMonth 
         
        lMaxRows = Cells(Rows.Count, 1).End(xlUp).Row 
             
        If lMaxRows < 2 Then GoTo Next_sMonth 
         
        Sheets(sSummarySheet).Range("A" & lSummaryRows & ":A" & lSummaryRows + lMaxRows - 1 - 1) = Sheets(sMonth).Range("A2:A" & lMaxRows).Value 
         
        lSummaryRows = lSummaryRows + lMaxRows - 1 
     
Next_sMonth: 
     
    Next sMonth 
     
    Sheets(sSummarySheet).Select 
    Columns("A:A").Select 
    Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _ 
                CopyToRange:=Range("B1"), Unique:=True 
    Columns(1).Delete 
                 
     For Each sMonth In sMonths 
         
        On Error Resume Next 
            Sheets(sMonth).Select 
        On Error GoTo 0 
         
        If (ActiveSheet.Name <> sMonth) Then GoTo Next_sMonth2 
         
        Sheets(sSummarySheet).Select 
         
        iMonth = WorksheetFunction.Match(sMonth, Range("1:1"), 0) 
         
        With Range(Cells(2, iMonth), Cells(lSummaryRows, iMonth)) 
            .FormulaR1C1 = "=IF(ISERROR(VLOOKUP(RC1, '" & sMonth & "'!C1:C2,2,false)),"""",VLOOKUP(RC1,'" & sMonth & "'!C1:C2,2,false))" 
            .Copy 
            .PasteSpecial xlPasteValues 
        End With 
         
             
     
Next_sMonth2: 
     
    Next sMonth 
                    
                    
End Sub 

Thanks for your help, but I have no idea what to do with this information! Is that a macro?

Is this easy to do? I'm not sure where to start, I'm an excel noob :(
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
761
Yes it is a macro

open the file
press ALT + F11
click on insert
add a new module
Copy the code between (both lines including

Sub SummarizeData()

End Sub


Run the macro (press F5)

Next time if you save the file with macro and you open the file. You would get a message about enabling macro. ENABLE THEM

Good Luck!!!!

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!