For Each / Loop / If on 2 Excel columns

[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

[MichaelAnnis · Accepted solution]

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.

[UK_Mike]

Hi Mike,

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

Thanks

[MichaelAnnis]

Is that in the LoopIndex Limit?

[UK_Mike]

Yes Mike

[UK_Mike]

[MichaelAnnis]

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

[UK_Mike]

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

[UK_Mike]

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

See you in part 2.... 🤣

Many thanks as always... 😘

[UK_Mike]

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

[MichaelAnnis]

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

[UK_Mike]

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