Answered

Reading from a VERY large excel spreadsheet

(0) Share

Report

Posted on by jharrity

Hi. I have a 2 column excel spreadsheet that has over 17,000 rows. I want to use a variable to identify a particular row of column A (the row that has a value that matches the value of the variable), and then read the value from column B for that particular row. Currently, I have a for each loop that reads from the first row, compares the value to the variable value, if they don't equal each other, then we read the next row, and so on until we find a match. When it matches, we read the value from column B. As you can imagine, this is taking forever to find that single row that has a value that matches the variable value.

How can I do this more quickly using Power Automate Desktop? Any help would be GREATLY appreciated.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (2)

Answers (1)

Sort by

Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @jharrity
It's not beautiful by any means, but it's waaay quicker than loop and works (tested) 😅:
1) 'Launch Excel' and choose your file
2) 'Get window' > foreground window (gives %AutomationWindow% as output variable)
3) 'Focus window' > Find window mode = By window instance/handle, Window instance = %AutomationWindow%
(Steps 2-3 are to assure PAD is with Excel window active and focused)
4) 'Write to Excel worksheet' > write in cell D1 (as you said your ws has only columns A and B filled) the value to be find in Column A
5) 'Send keys' > {Down}, {F2}
6) 'Send keys' > =VLOOKUP(D1,$A:$B,2,0) {Enter}
7) 'Read from Excel worksheet > get value in D2 cell

Was this reply helpful? Yes No
jharrity 42 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks! That worked. The lookup is lightening quick now.

Was this reply helpful? Yes No