Excel VBA - Capture the value in one column and search for it in another column

[jeaneshe]

Hi all
I've tried this a multitude of ways and none work. My assignment is to take one text value in A1 and search D for that value. I've been somewhat successful, except I'm doing it from my macro editor because I don't know Excel VBA well and can't seem to fudge it this time. I have trouble dimensioning correct variables plus much of it just doesn't work for me yet. I am constantly trying to use VBA and sometimes it actually works!

Scenario: Sheet1 has a column of records (they have leading zeros so their format is Text). For each of those records, I search a column in Sheet2. If it is found on Sheet2, notate the column to the right. Then go back to Sheet1, capture the next record, go to Sheet2, search for the record. Do this until all records in Sheet1 have gone to Sheet2 and searched the column.

I don't have VBA to send, but I can send an Attachmate macro code that sort of works but takes all day looping through the thousand records. the attachment field would not allow me to attach a document so I'm pasting it here.

Please help! Thanks so much!
'--------------------------------------------------------------------------
Sub Main()

Dim xl1 as Integer, xl2 as Integer, xl3 as Integer, RC, x
Dim xlIntId as string, i as Integer, fndIntId as String, tstver as string

'OPER WORKBOOK
Call GetExcel ()

'BEGIN WITH ROW 2 FOR ALL SHEETS
xl1 = 2 ' IntrlIds row number last used
xl2 = 2 ' FindId
xl3 = 2 ' PostRec row number last used

'LOOP THROUGH ALL IntrlIds
Do
'GET INTERNAL ID FROM IntrlIds SHEET
xlIntId = objWorkbook.Worksheets("IntrlIds").cells(xl1,1) Do
'GET FNDiNTID
fndIntId = Trim(objWorkbook.Worksheets("FindId").cells(xl2,21)) 'COUNTS THROUGH ALL FindId RECORDS
'COMPARE IDS
If (xlIntId = fndIntId) Then
'Call MvRec()
objWorkbook.Worksheets("FindId").cells(xl2,22) = fndIntId
objWorkbook.Worksheets("IntrlIds").cells(xl1,2)= "y"
xl2 = 2
goto nextacct
End if
xl2 = xl2 + 1
Loop until fndIntId = ""
If fndIntId = "" then
xl2 = 2
End if
NextAcct:
xl1 = xl1 + 1
xlIntId = objWorkbook.Worksheets("IntrlIds").cells(xl1,1)

Loop until xlIntId = ""

End Sub

 

[SkipVought]

Hi,

...they have leading zeros so their format is Text

Just because this column displays leading zeros, does not mean that the column is formatted as TEXT. A range can be FORMATTED to display leading zeros without actually having leading zeros. This could possibly be the reason that you code is not working. Just a guess.

BTW, is you can see leading zeros in the Formula Bar, then you have Text with actual leading zeros. Otherwise, the leading zeros are simply a Display Feature of a Number Format.

It would be helpful if you would upload the workbook containing this data. The actual data is the key. Unless we can see your actual data, posting code alone, means very little.

And why Attachmate VB? Why not VBA from Excel? Attachmate VB vs VBA is like a Vega vs a Cadillac.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Also, if you insist to show your code, why not show it as code?

Sub Main()

 Dim xl1 as Integer, xl2 as Integer, xl3 as Integer, RC [red]As Variant (not used)[/red], x [red]As Variant (not used)[/red]
 Dim xlIntId as string, i as Integer, fndIntId as String, tstver as string
[green]
 'OPER WORKBOOK[/green]
 Call GetExcel ()
[green]
 'BEGIN WITH ROW 2 FOR ALL SHEETS[/green]
 xl1 = 2 [green]' IntrlIds row number last used[/green]
 xl2 = 2 [green]' FindId[/green]
 xl3 = 2 [green]' PostRec row number last used[/green]
...

---- Andy

There is a great need for a sarcasm font.

 

[jeaneshe]

For some reason I've lost this reply 2 times already. sorry If this is lacking.

I'm not concerned about the values in the cells containing leading zeros. I can fix that if I need to. My problem is that I have to capture a value in Column1 and then search through all the records in column2 and if it is found, notate that record.

If you can imagine how long it take when I have thousands of records to perform this on.

Essentially if the value from column 1 is located in column 2 it means I'll want to move that entire row to column 3 (not started on the column 3 functionality yet"

I'm not sure how to attach a file without exposing proprietary data. I'll put a sample of the spreadsheet below.

RecFailSheet Find this in next sheet.
0000000004932
0000000021159
0000000021413
0000000069611
0000000171454
0000000309067
0000000380205
0000000511736

Second sheet records
0000000021159
0000000171454
0000000380205
0000000511736
0000000556477
0000000730676
0000000737373

 

[SkipVought]

Well here's a quik n dirty representation of your data on one sheet for clarity.

2 tables (Structured Tables). The Sheet1 table also has a column I added named Sheet2 with a formula that you can view in the Formula Bar. This formula would look exactly the same, if the Sheet2 table (Table4) were on another sheet. You will notice that there are 4 Sheet1 values that were found in the Sheet2 table in rows 1,2,3&4. There are also 4 NOT FOUND (#N/A)

So once a lookup is successfully made, any values in that row on Sheet2 cane quite easily be displayed, using the INDEX() function, for instance.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[jeaneshe]

Hi Skip

Interesting, I'm just curious that the numbers that made a match are not indicated by that cell. I need this to perform a sort of those that are found specifically. When there is a match, I must somehow select the entire row and move it to another sheet. Or do a sort on the column of results and if there is an indicator next to a matching number, I'll sort the whole sheet by that column and select and move the matched records then.

I've not used the index function before. Can you give me an example relating to the example above? I sure appreciate your help!

 

[strongm]

Surely you just need an Excel function something like

=IF(IFNA(MATCH(A1,Sheet1!A:A,0),FALSE),"y","")

in sheet 2 column 2

 

[jeaneshe]

So sorry but that did not work me. Thank you

 

[strongm]

What, my formula?

This is what I get:

Be aware that I complicated the formula to match what your code was trying to do - put 'y' against matches. However, it was designed to allow us to move on to the next stage (which would use INDEX like SkipVoight is suggesting), and removing the outer IF means we can get:

I attach a spreadsheet that illustrates this

 

[Andrzejek]

I'm not sure how to attach a file without exposing proprietary data

Simple.
Create an Excel file with some bogus data (we don’t care here is the data is legit or made up) representing your issue, and also present in the same (or other) file what the solution should look like, so people who want to help you don’t have to guess.



---- Andy

There is a great need for a sarcasm font.

 

[jeaneshe]

Ok. Thanks I have washed it, but I take the first number in the first tab, then search for that number in all col 21. If I find it, I paste the number in col 22.
Then I take the next number in Sheet one and do the same in sheet 2. There will be 1000's of records potentially. I intend to try Skip index idea. Since I haven't ever used that command, and I really suck at VBA I'll need to find time to study it. So sad. Also this file may have some VBA I've tried if you are interested. Thanks again.

 

[strongm]

Still a bit opaque what you are trying to do.

We have a list of numbers in column 1 on sheet 1 (IntrlIds)
We have another list of numbers in column 22 on sheet 2 (FindID)

For each number in list 1 we want to see if there is a match in list 2
Wherever there is a match we want to record the matched number in column 23 on sheet 2 (FindID) in the same row the match is found
Additionally want to put a 'y' in the column 2 on sheet 1 (IntrlIds) in the same row as the number n that was matched

Ok, so unclear to me why you need to do BOTH of those things (or, indeed, either of them) - it is presumably just so you can see which numbers have been matched, and use that for figuring out which rows to use "to move that entire row to column 3" (but it is unclear what you really want here as well! Move an entire row into a column? Shift the rows to the right by two columns? Column 3 on sheet 1 or sheet 2? Or is this really just meaning a 3rd column is going to get involved, but that column number could be, say, 23?)

Unfortunately your spreadsheet doesn't help clear up any of my confusion.

>I haven't ever used that command, and I really suck at VBA I'll need to find time to study it

INDEX is an Excel function, noit VBA. And I illustrate its use in the spreadsheet I previously attached.

 

[SkipVought]

Here's the "Skip INDEX()idea."

I made the tables Structured Tables. So much easier to write and dicument.

But here's A1 notation...
[tt]
Sheet1!B2: =INDEX(Sheet2!B:B,MATCH($A2,Sheet2!$U:$U,0),1)
[/tt]
This formula takes the value in Sheet1!A1 and finds the row offset in Sheet2!$U:$U and the returns the corresponding row value from Sheet2!B:B. Assuming that that formula is in Sheet1!B2 (it could be in any column in Sheet1), but if you copy that cell and then SELECT across some number of cells, Right-Click PASTE, it will return the corresponding columns from Sheet2 row 2, into which the formulas reside. Then COPY those cells and SELECT down, Right-Click and PASTE--and you have data in all relevant columns for all relevant rows.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[strongm]

Yep, I've got pretty much the same solution

 

[Andrzejek]

Not much of a sample representing your issue and no hint of what the solution should look like... :-(
Back to the guessing game....


---- Andy

There is a great need for a sarcasm font.