UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Loop Macro, Office 2013    
 
   
freespiritcheris...
post Sep 26 2019, 07:17 PM
Post#1



Posts: 155
Joined: 19-October 04



Hello UA friends..
I'm seeking assistance.. again reading.gif

NB: There are no headers, the cell references are as would be and not as in pic.

I want to check if the values in the Colums of Block A exist in the columns of Block B. I need a macro that will check to see if the value of A:1, to G:1 exists within the range I:1 to BE:1. If ALL cells A1 to G1 are present in row 1, I:1 to BE:1, then insert "TRUE" in column BG, if not then to insert "FALSE" then go to next row, I:2 to BE:2 and check again to see if A:1 to G1 exists. In real life, there are 5,000 rows in Block A and 9,030 rows in Block B and each row of the 5,000 of Block A has to be checked against all 9,030 rows of Block B, one at a time.. I have no idea on how to even begin. Obvio a Loop Macro in vba is needed but how? Massive task, more than I can chew to be honest, but I am willing to learn.. well.. copy and paste first, then do the learning bit at the 'pulling out my hair and stuffing my face stage'! Any pointers/code snips/examples, would be very useful and much appreciated.

Ive attached the spreadsheet just in case someone can do something with it.
many thanks

Attached File(s)
Attached File  testxl.JPG ( 200.74K )Number of downloads: 8
Attached File  Testxl_headache_come_hither.zip ( 8.99K )Number of downloads: 4
 
Go to the top of the page
 
June7
post Sep 26 2019, 09:04 PM
Post#2



Posts: 967
Joined: 25-January 16



Here is one way:
CODE
Function test(intRow As Integer) As Boolean
Dim r As Integer, c As Integer, intC As Integer, strNums As String
With Worksheets("Sheet1")
    For c = 9 To 57
        strNums = strNums & .Cells(intRow, c) & ", "
    Next
    For r = 2 To 8
        For c = 1 To 7
            If InStr(strNums, " " & .Cells(r, c)) > 0 Then intC = intC + 1
        Next
        If intC = 7 Then
            test = True
            Exit For
        End If
    Next
End With
End Function

Can call function from cells: =test(ROW(A2)) - just fill down and the row reference will automatically adjust.

Instead of just TRUE or FALSE, code could modified to return Block A row number that meets condition.

Can further automate to dynamically determine top limit of loops as well as to populate cells instead of calling from cell.
This post has been edited by June7: Sep 26 2019, 09:58 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
June7
post Sep 26 2019, 10:15 PM
Post#3



Posts: 967
Joined: 25-January 16



Had to modify code for logic error.
CODE
Function test(intRow As Integer) As Boolean
Dim r As Integer, c As Integer, intC As Integer, strNums As String
With Worksheets("Sheet1")
    For c = 9 To 57
        strNums = strNums & .Cells(intRow, c) & ","
    Next
    For r = 2 To 8
        intC = 0
        For c = 1 To 7
            If InStr(strNums, "," & .Cells(r, c)) > 0 Then intC = intC + 1
        Next
        If intC = 7 Then
            test = True
            Exit For
        End If
    Next
End With
End Function

Block B first row has a match to Block A row 6. Your sheet shows False.

This post has been edited by June7: Sep 26 2019, 10:21 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
arnelgp
post Sep 26 2019, 10:52 PM
Post#4



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


here is your spreadsheet.

i think the earlier solution will not work becoz
on cell A2=2, most cells on block B has more
than 2 counts of "2".
Attached File(s)
Attached File  Testxl_headache_come_hither.zip ( 10.87K )Number of downloads: 2
 

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
June7
post Sep 26 2019, 11:03 PM
Post#5



Posts: 967
Joined: 25-January 16



Don't understand what you mean by that statement. My code works.

User said BlockA will have 5,000 rows and BlockB 9,030. How will your approach accommodate that?

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
arnelgp
post Sep 26 2019, 11:18 PM
Post#6



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


block "A", range A2, value is 2.
on block "B", check how many 2's
on one row.

then you concat block B.

if you use instr(blockB, 2), you will
get more than 1.
therefore comparing to total 7, you
will never get it.

my approach is using exact value on block A
matching each column in Block B.

double click on the left-bottom square
on my formula, will copy it up to last row.

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
June7
post Sep 27 2019, 12:47 AM
Post#7



Posts: 967
Joined: 25-January 16



It does work. I get the same 2 True returns that you do.

No, Instr() returns position number for first match encountered, it doesn't count all occurrences of a value.

Issue would occur if there are duplicate values in row of BlockA, not BlockB, but sample does not show that case.

But if can be done without VBA, that is probably preferable.

This post has been edited by June7: Sep 27 2019, 12:53 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
freespiritcheris...
post Sep 27 2019, 04:06 AM
Post#8



Posts: 155
Joined: 19-October 04



Woooowwww you guys!!

Thank you so much!!! I am in absolute awe..

Hi june

Apologies, you're absolutely right.. there is a logic error!... I don't suppose you would consider placing that code in the attachment and send it back to me so that I can see where the code is placed and how it is working?
This post has been edited by freespiritcherishes: Sep 27 2019, 04:36 AM
Go to the top of the page
 
freespiritcheris...
post Sep 27 2019, 04:31 AM
Post#9



Posts: 155
Joined: 19-October 04



Hi arnelgp

Thank you so much for the example, it really does make things easier to understand if one can see it in action. I have to be somewhere right now, but will give a proper reply later when I've used it. But on brief look, will the True False colums on the right increase as the rows increase in Block A? 5,000 plus TRUE FALSE columns??
This post has been edited by freespiritcherishes: Sep 27 2019, 04:48 AM
Attached File(s)
Attached File  wtgw.JPG ( 188.3K )Number of downloads: 2
 
Go to the top of the page
 
June7
post Sep 27 2019, 04:47 AM
Post#10



Posts: 967
Joined: 25-January 16



My second version works. VBA is just in a general code module in the VBA editor. But don't really need it with arnelgp's cell formula.

This post has been edited by June7: Sep 27 2019, 04:48 AM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
freespiritcheris...
post Sep 27 2019, 05:06 AM
Post#11



Posts: 155
Joined: 19-October 04



Hi June,

It happens that in my real data there does appear to be some rows with a duplicate value in Block A. I selected random data that happened not to show it. How would I get around this using your code?

Go to the top of the page
 
freespiritcheris...
post Sep 27 2019, 02:43 PM
Post#12



Posts: 155
Joined: 19-October 04



Forget that last message about the duplicates!
Go to the top of the page
 
freespiritcheris...
post Sep 27 2019, 03:36 PM
Post#13



Posts: 155
Joined: 19-October 04



Well June,

I have had a look and love the idea of what it can do but I have absolutely no idea what to do. It is sitting in my vba window returning Test() asking for data, I select the range I:1 to BE:1 as and I get VALUE# error. What am I doing wrong.. what must I do with the function. I would really like to see it working dontknow.gif I thought it would be a macro as that is usually simpler for me to use. Can this be converted to a macro?

Thanks in advance.

This post has been edited by freespiritcherishes: Sep 27 2019, 03:37 PM
Go to the top of the page
 
June7
post Sep 27 2019, 03:53 PM
Post#14



Posts: 967
Joined: 25-January 16



I explained in post2 how to call function from cell:

Can call function from cells: =test(ROW(A2)) - just fill down and the row reference will automatically adjust.

I put VBA procedure in a general code module. Copy/Paste into module.



--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
freespiritcheris...
post Sep 27 2019, 04:53 PM
Post#15



Posts: 155
Joined: 19-October 04



Sorry June, I still don't understand.

If I place =test(ROW(A2)) in cell A1, A2 or even in the formula bar (my table does not have headers) I get "#Name?". I don't know where else to put it.

I have never called a function from a cell before. What do you mean fill down? I was just going to copy and paste my rows over the existing table with the function/macro ready to run, then observe what it was doing. I apologise for my ignorance I learn visually rather than from explanations as I am not a coder.
Go to the top of the page
 
June7
post Sep 27 2019, 05:51 PM
Post#16



Posts: 967
Joined: 25-January 16



Did you open VBA editor and create a module and copy/paste function code?

If you don't have headers on row 1 as in posted database, code will have to be modified slightly to have outer loop start with 1 instead of 2:

For r = 1 To 8

And the formula would start in cell on row 1 with: =test(ROW(A1))

Several ways to fill cells down. One way is click/grab/drag lower right corner of cell. But 9030 rows would be a long drag. So, review https://www.wallstreetmojo.com/excel-fill-down/
Would need to do this with arnelgp's approach as well.

A workbook with VBA code must be saved as an xlsm file.

Macros in Excel are really just VBA code.

This post has been edited by June7: Sep 27 2019, 05:53 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
freespiritcheris...
post Sep 28 2019, 03:41 AM
Post#17



Posts: 155
Joined: 19-October 04



Hi arnelgp

Excellent!!! Totally works and is doing the job, thank you so much! notworthy.gif

I shall feed back more later when I have finnished processing the most recent data and share on here. Thanks again!
This post has been edited by freespiritcherishes: Sep 28 2019, 03:43 AM
Go to the top of the page
 
arnelgp
post Sep 28 2019, 09:46 AM
Post#18



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


this is much easier.
on the ribbon, Home, right side, there is a Special Function Menu.
click it and just provide the range for block A, block B
and the single Output cell.

it will fill the cells, starting with the
Output cell with the result.
Attached File(s)
Attached File  Testxl_headache_waiting_to_happen.zip ( 31.33K )Number of downloads: 1
 

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 
freespiritcheris...
post Sep 30 2019, 07:19 AM
Post#19



Posts: 155
Joined: 19-October 04



woohoo.gif dance.gif pompom.gif applause.gif WOW

THANK YOU SO MUCH arnelgp!!!!! notworthy.gif notworthy.gif
Go to the top of the page
 
arnelgp
post Sep 30 2019, 08:05 AM
Post#20



Posts: 1,510
Joined: 2-April 09
From: somewhere out there...


you're welcome!

--------------------
Never stop learning, because life never stops teaching.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th November 2019 - 04:11 PM