Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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



  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default Compare 2 Lists and Take an Action Q

Thanks guys, first part completed

Stage 2 now...

  #10   Report Post  
Banned
 
Posts: 7
Default

Cho mình một vé
_________________
kinky videos

https://t.co/2K1zjCFslH


  #11   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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


  #13   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default Compare 2 Lists and Take an Action Q

Thanks Claus, that did it. I find it hard to understand Bound statements


  #14   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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
  #16   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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

  #18   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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)
  #20   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default Compare 2 Lists and Take an Action Q

Claus, just e-mailed you, let me know if you don't get

Thanks



  #21   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3,872
Default Compare 2 Lists and Take an Action Q

Hi Sean,

Am Tue, 22 Nov 2016 01:35:02 -0800 (PST) schrieb :

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


try:

Sub ExtractUnmatched()
Dim myCnt As Long, LRow As Long
Dim c As Range

With Sheets("Reconciliation")
LRow = .Cells(.Rows.Count, "M").End(xlUp).Row
.Range("M1:Q" & LRow).AutoFilter Field:=5, Criteria1:="="
myCnt = Application.Subtotal(3, .Range("M:M")) - 1
If myCnt 14 Then Sheets("Report").Rows(28).Resize(myCnt - 15).Insert
.Range("N2:N" & LRow).Copy
Sheets("Report").Range("B14").PasteSpecial xlPasteValues
.Range("O2:O" & LRow).Copy
Sheets("Report").Range("A14").PasteSpecial xlPasteValues
.Range("P2:P" & LRow).Copy
Sheets("Report").Range("H14").PasteSpecial xlPasteValues
.AutoFilterMode = False

LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set c = Sheets("Report").Range("A:A").Find("Payments",
LookIn:=xlValues, lookat:=xlPart)
LRow2 = c.Row + 2
.Range("A1:K" & LRow).AutoFilter Field:=11, Criteria1:="="
myCnt = Application.Subtotal(3, .Range("A:A")) - 1
If myCnt 7 Then Sheets("Report").Rows(LRow2).Resize(myCnt - 7).Insert
.Range("B2:B" & LRow).Copy
Sheets("Report").Range("A" & LRow2).PasteSpecial xlPasteValues
.Range("D2:D" & LRow).Copy
Sheets("Report").Range("B" & LRow2).PasteSpecial xlPasteValues
.Range("G2:G" & LRow).Copy
Sheets("Report").Range("H" & LRow2).PasteSpecial xlPasteValues
.AutoFilterMode = False
End With

End Sub

I sent you back your workbook.


Regards
Claus B.
--
Windows10
Office 2016
  #24   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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
  #27   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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

  #30   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 73
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare two lists RichUE Excel Worksheet Functions 2 December 15th 08 03:29 PM
compare two lists lark Excel Worksheet Functions 3 May 6th 08 02:12 AM
Compare Two Lists Paul Black Excel Programming 8 October 27th 06 02:01 PM
Compare 3 lists [email protected] Excel Programming 2 July 6th 06 08:28 PM
Compare two sheets and perform action routine wayliff[_9_] Excel Programming 2 January 16th 06 09:28 PM


All times are GMT +1. The time now is 10:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"