Matching data in Excel Closed

Question

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





Configuration: Windows XP / Firefox 3.5.7

rizvisa1 · Answer

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

Excelbeginner · Answer

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!

rizvisa1 · Answer

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

Excelbeginner · Answer

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 :(

Matching data in Excel

4 responses

Similar discussions

Subscribe To Our Newsletter!