cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
UK_Mike
Post Prodigy
Post Prodigy

For Each / Loop / If on 2 Excel columns

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

1 ACCEPTED SOLUTION

Accepted Solutions
MichaelAnnis
Super User
Super User

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.

 

 

View solution in original post

11 REPLIES 11
MichaelAnnis
Super User
Super User

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.

 

 

Hi Mike,

 

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

 

 

Thanks

 

Is that in the LoopIndex Limit?

Yes Mike

Screenshot 2022-02-01 173046.pngScreenshot 2022-02-01 173106.png

%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.

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

Worked perfectly, just have to apply this rationale to the actual flow.

See you in part 2.... 🤣

 

Many thanks as always... 😘

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...

 

Screenshot 2022-02-02 174800.pngScreenshot 2022-02-02 174829.png

 

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).

Initially I thought of just taking that %List% produced by the IF and using it in another IF but thought there must be a way.

Then i tried %List[LoopIndex}%.....

If only I tried %ColumnAsList_ID[LoopIndex]%  😂

Many thanks for all your help, really appreciated !

 

RESULT !!!!!!!!!!

 

Screenshot 2022-02-02 192754.png

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Kudoed Authors
Users online (2,850)