upobDaPlaya
asked on
How do I in Excel VBA update Excel rows using SQL within VBA
If I have 2 sheets let say students and classes and I want to update the students tab with the class id found in the class tab would I be better off creating a SQL update rather than looping thru the class tab multiple times. If the answer is yes can somebody give me an example of the SQL using I assume an ADO connection...
ASKER
Here is the file..I simply want to update the second tab with the Invoice from tab 1 provided I match on the amt, store, and date, I realize there are multiple Invoice numbers for this criteria, but that is fine. The key is to get the invoice number on the second tab. Once I do that I am going to loop thru the source tab and see what invoice numbers were not found and put them in the not matched tab.
See not matched tab for results.
ee-Not-Matched.xlsx
See not matched tab for results.
ee-Not-Matched.xlsx
in Total's cell D2, try use a Array formula such as:
NOTE: For array formula, so press Ctrl + Shift + Enter, instead of just pressing the Enter key.
and drag down for other cells.
=INDEX(Source!A:A, MATCH(1,(Today!A2=Source!B:B)*(Today!B2=Source!C:C),0))NOTE: For array formula, so press Ctrl + Shift + Enter, instead of just pressing the Enter key.
and drag down for other cells.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was trying to avoid an array as they seem pretty slow for any dataset over 1000 ?
I was trying to avoid an array as they seem pretty slow for any dataset over 1000 ?You can test that if the performance is acceptable after applying the array formula.
Alternatively, for a more "traditional" way, you may consider to combine the cell's value and then use a simpler Index + Match formula to compare values.
Check the attached for more info.
ee-Not-Matched_c.xlsx
can you share a sample file here and point out the expected results?