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
- How to take screenshot in excel - Guide
- Tmobile data check - Guide
- Dd-mm-yyyy format 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!!!!
