For Each / Loop / If on 2 Excel columns

(0) Share

Report

Posted on by UK_Mike

Hi All,

Excel, in Sheet1 I have 2 columns with 5 rows, keeping it simple.

Column 1 has unique account numbers, 1001,1002,1003,1004,1005.

Column 2 has the account status, its either DR or CR.

If "DR" then skip, If "CR" then print the account number (1001 etc).

I have retrieved both columns into separate lists, im getting output, no errors, just not the desired result.

More often than not im getting all the values from column 1 ( account numbers ) written rather than the individual account number.

There's a bit more to the flow but the above explains the guts of it...

Thanks

Categories:

Power Automate Desktop

I have the same question (0)

All responses (11)

Answers (1)

Verified answer

MichaelAnnis 5,727 Moderator on at

Like (1)

Report

Once you have both columns in separate lists (%Accounts% and %Status%), and assuming there is no title row in either, so [0] is value 1 and value 1 of %Accounts% directly corresponds with value 1 of %Status%:

Create list %CreditAccounts%
Loop 0 to %Accounts.Count - 1% increment of 1
If %Status[LoopIndex]% = CR
Add %Accounts[LoopIndex]% to list %CreditAccounts%
End (If)
End (Loop)

At the end, %CreditAccounts% is a list of only your credit accounts.

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report

Hi Mike,

Parameter 'End to': Operator '-' can't be applied to 'Text value'.

Thanks

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report

Is that in the LoopIndex Limit?

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report

Yes Mike

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report

%ColumnAsList_ID.Count - 1% .Count is a property of a list which counts the number of rows in the list. Because it is a 0 based index, we take 1 off. So, if there are 20 rows, we got [0] to [19] to represent rows 1-20.

Good luck.

Was this reply helpful? Yes No
UK_Mike on at

Like (1)

Report

FUC###################################

Really sorry Mike, school boy error on my part.
Jesus ive incorporated that .count a million times in other flows...
Error resolved
Will get back to ya 😂😂😂😂

Many thanks Mike

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report

Worked perfectly, just have to apply this rationale to the actual flow.
See you in part 2.... 🤣

Many thanks as always... 😘

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report

Hi Mike,

I think I jumped the gun on my end, your solution worked perfectly but there was only one value to write, as in
IF =CR "Then do this " writes the value correctly.
However when there are 2 or more correct values that = CR it writes incorrectly.
Ive added a shot of the input and output columns.
All the variable lists produced are correct, especially the %List% which contains 1002,1005,1008.
Hope it makes sense...

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report

Because you are writing the list on every loop.

First loop, 1002
Second loop, 1002 and 1005
Third loop, 1002 1005 and 1008

If you want to do it this way, you can:
Option 1, take everything but "Add item to list" out of the If...then write the list 1 time at the end to D2

OR

Option 2, inside the If, on "Write to Excel" replace "List" with "ColumnAsList_ID.[LoopIndex]%

Good luck. The point of the List was so that you have them all in a list inside of PAD, but if you need that list copied somewhere, you can copy it as you go (Option 2), or write the list after the whole thing is done (Option 1).

Was this reply helpful? Yes No