Matching data in Excel
Closed
Excelbeginner
-
Jun 10, 2010 at 05:31 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 10, 2010 at 11:08 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jun 10, 2010 at 11:08 AM
Related:
- Matching data in Excel
- Transfer data from one excel worksheet to another automatically - Guide
- Number to words in excel - Guide
- Tmobile data check - Guide
- Gif in excel - Guide
- Marksheet in excel - Guide
4 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 10, 2010 at 05:35 AM
Jun 10, 2010 at 05:35 AM
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!
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
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 10, 2010 at 11:00 AM
Jun 10, 2010 at 11:00 AM
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 :(
Is this easy to do? I'm not sure where to start, I'm an excel noob :(
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Jun 10, 2010 at 11:08 AM
Jun 10, 2010 at 11:08 AM
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!!!!
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!!!!