Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
I have 2 Lists of Data on the same sheet
List A is detailed in Columns A-D List B is detailed in Column G-K What I want to do is compare certain Columns/Rows in both Lists and for each one that matches, place an X in Column E (covering List A) and a corresponding X in Column L (covering List B). Note the matched values will not be on the same Row in both So what will be compared? if Col B & D rows matches somewhere with Col H & K an X is placed on the appropriate Row in Cols E & L Essentially I want to do a Supplier A/c Reconciliation. Above would give me a good start point |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Hi Garry,
Am Fri, 18 Nov 2016 14:46:43 -0500 schrieb GS: .Cells(i, "E") = "x" .Cells(i, "L") = "x" ??s/b?? .Cells(j, "L") = "x" yes, you are right. It should be .cells(j,"L") Regards Claus B. -- Windows10 Office 2016 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Thanks Guys
What is it with copying the code from these forums, I always get syntax errors and end up removing all spaces from leading and end of each line? One Q, I've no data (yet), but when I ran code it placed an X in Row 1 E&L, it should be nothing, just a quirky one at this stage |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
If you have headers, change the code:
I'd leave the headers included in the 2D arrays, just start at row2... For i = 2 To LBound(varA) For j = 2 To LBound(varB) ...so no row offset is required!! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
What is it with copying the code from these forums, I always get
syntax errors and end up removing all spaces from leading and end of each line? Wordwraps and/or linewraps in the news reader... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Thanks guys, first part completed
Stage 2 now... |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
I've got some test data, and I've hit a speedbump, nothing matches, even when I deliberately edit to match an entry in Cols 4 & 7 to Cols 2 & 4.
I've edited the Column details from original, now matching D&G to N&P Sub CompareList() Dim LRowA As Long, LRowB As Long, i As Long, j As Long Dim varA As Variant, varB As Variant Sheets("Reconciliation").Select With ActiveSheet LRowA = .Cells(.rows.Count, "A").End(xlUp).Row LRowB = .Cells(.rows.Count, "M").End(xlUp).Row varA = .Range("A1:J" & LRowA) varB = .Range("M1:P" & LRowB) For i = 2 To LBound(varA) ' start in row 2 to allow for headers For j = 2 To LBound(varB) ' start in row 2 to allow for headers If varA(i, 4) & varA(i, 7) = varB(j, 2) & varB(j, 4) Then ..Cells(i, "K") = "x" ..Cells(j, "Q") = "x" Exit For 'If there is only one match possible delete the apostroph before this line End If Next Next End With End Sub |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Hi Sean,
Am Mon, 21 Nov 2016 08:09:06 -0800 (PST) schrieb : For i = 2 To LBound(varA) ' start in row 2 to allow for headers For j = 2 To LBound(varB) ' start in row 2 to allow for headers you have to loop from second value to last value. The last value of an array is Ubound *NOT* LBound Change the two lines to: For i = 2 To UBound(varA) ' start in row 2 to allow for headers For j = 2 To UBound(varB) ' start in row 2 to allow for headers Regards Claus B. -- Windows10 Office 2016 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Thanks Claus, that did it. I find it hard to understand Bound statements
|
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Next Step in my Automated Reconciliation
I need to Copy only Rows that do not have an "X" within to a Specific Area of "Sheet3" So the Area I am looking to Copy from is Cols N-P (starting at Row2), note the "X" is detailed or not in Col Q The Range will be variable, so this month it might be 500 rows, next could be different Sheet3 will layout as follows ColO should appear in ColA, starting at Row14 ColN should appear in ColB, starting at Row14 ColP should appear in ColH, starting at Row14 The twist on Sheet3 is that I have a certain layout, so if I I'm copyinfg in 60 rows of data, there needs to be 60 rows available starting at Row14 in Sheet13 if that makes sense, as I have a Subtotal Row currently on Sheet3 Row 29 Below is my Recorded VBA filter, but I'm stuck at the re-arranging of the Columns and inserting the rows on Sheet3 Sub ExtractUnmatched() Sheets("Reconciliation").Select Columns("M:Q").Select Selection.AutoFilter ActiveSheet.Range("$M$1:$Q$5000").AutoFilter Field:=5, Criteria1:=" " Range("N2:P5000").Select Selection.Copy Sheets("Sheet3").Select Range("A14").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A1").Select Sheets("Reconciliation").Select Selection.AutoFilter Range("A1").Select End Sub |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
|
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Claus, that's exactly what I wanted Re Row insert. This is coming together nicely
I now need to Post another extract below the last piece, but instead of knowing I start at Row14, as above, I now need to post the next chunk 8 rows below the last data ended. I'll filter similar to last chunk, but this part - LRow = .Cells(.Rows.Count, "A").End(xlUp).Row .Range("A1:K" & LRow).AutoFilter Field:=11, Criteria1:="=" myCnt = Application.Subtotal(3, .Range("A:A")) - 1 If myCnt 14 Then Sheets("Report").Rows(28).Resize(myCnt - 15).Insert I'm unsure how I can adjust it to post 8 rows below and once again insert rows etc if the filter Data is larger than available (at the moment I'm working off 7 rows available) As they are a reconciliation, when I set up a new Rec I'll clear all data first and delete unwanted Rows on Sheet3 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
|
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Claus, I have 2 sections to the Reconciliation, first section (which works perfectly from your filter code yesterday) inserts all Invoices on Supplier not on Company books, thats what is inserted from Row 14
Second section now inserts all Invoices on Company not on Supplier, so I will insert these using a 2nd filetr on different Data, but I need to insert these starting 8 Rows below First section, and insert rows if I don't have enough available, hence I mentioned I only have 7 in my layout The part of the file I'm working on now is like a template, so any rows from the 2 filters I have pulling in data, I need to insert on this template i..e. "Report" sheet I would post a Snapshot, but can't see any button (I'm using IE 11) |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
|
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Claus, just e-mailed you, let me know if you don't get
Thanks |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Hi Sean,
Am Tue, 22 Nov 2016 00:40:08 -0800 (PST) schrieb : Claus, just e-mailed you, let me know if you don't get I got it. You filter range A:K. What columns do you want to copy and where should they go in sheet "Report"? Regards Claus B. -- Windows10 Office 2016 |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Claus, I need to copy
B; D; G, these should be copied to Columns A; B; H The rows for this 2nd filter should start at Row278, but as explained, this is dynamic, it depends on the length of Filter # 1 Thanks for your help, much appreciated |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
|
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Perfect Claus, many thanks for your assistance
Just a few things to tidy up now. When I'm setting up a new Reconciliation, how do I delete the rows on the 'Report' sheet that the Filters are copied to? I want to do this just to have a neat layout at start point, as the Filter Code will insert if not enough Rows anyway |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
|
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
|
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
I like your idea of code to delete, but I couldn't get it quite to work, some of the template formulas were not in the correct place. Ended up with a 'Template' sheet and copying that in
Sub TidyupRows() Sheets("Template").Visible = True Sheets("Template").Select Cells.Select Selection.Copy Sheets("Template").Visible = False Sheets("Report").Select ActiveSheet.Paste Range("A1").Select End Sub |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
|
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
|
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Thanks Claus, its working pretty well. I'm sure I'll think of tweaks to enhance it
|
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Claus, I have 2 Macro Buttons on the Report sheet, I will replicate these on the Template sheet, but how does your code below handle these, does it delete them from Report first? I've had other worksheets when the size explodes when you copy sheets in multiple times with Buttons
Sub RecNew() Sheets("Report").UsedRange.ClearContents Sheets("Template").UsedRange.Copy Sheets("Report").Range("A1").PasteSpecial xlPasteAll End Sub |
#32
Posted to microsoft.public.excel.programming
|
|||
|
|||
Compare 2 Lists and Take an Action Q
Hi Sean,
Am Tue, 22 Nov 2016 12:19:37 -0800 (PST) schrieb : Claus, I have 2 Macro Buttons on the Report sheet, I will replicate these on the Template sheet, but how does your code below handle these, does it delete them from Report first? I've had other worksheets when the size explodes when you copy sheets in multiple times with Buttons ClearContents only deletes cell values. Other things like buttons will remain. Regards Claus B. -- Windows10 Office 2016 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare two lists | Excel Worksheet Functions | |||
compare two lists | Excel Worksheet Functions | |||
Compare Two Lists | Excel Programming | |||
Compare 3 lists | Excel Programming | |||
Compare two sheets and perform action routine | Excel Programming |