Iterative lookup

[YLYONG]

Hi fellow Alteryx community member,

Need to pick on your brains to solve this:

LOAD ID is the intended output 

Please let me know if you needed further clarification 😀

thank you

					OUTPUT
VENDOR	VENDOR SUBGROUP	LOAD # ASSIGNMENT	LOAD COUNT BY VENDOR SUBGROUP	Sum_LOAD COUNT BY VENDOR	LOAD ID
921338	-	1	1	1	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	1	3	8	1
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	2	3	8	2
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	98304	3	3	8	3
96733	-	1	1	8	4
96733	-	1	1	8	4
96733	-	1	1	8	4
96733	-	1	1	8	4
96733	-	1	1	8	4
96733	-	1	1	8	4
96733	-	1	1	8	4
96733	-	1	1	8	4
96733	-	1	1	8	4
96733	-	1	1	8	4
96733	-	1	1	8	4
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	1	4	8	5
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	2	4	8	6
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	3	4	8	7
96733	98305	4	4	8	8
96733	98305	4	4	8	8
921338	-	1	1	1	1

[Qiu · Accepted solution]

@YLYONG 
I use a Multi-Row Tool to achive the same output you specified. 😂

[YLYONG · Accepted solution]

Thank you @Qiu for your help!

it works for the majority of the scenarios except the following 🤔

VENDOR	VENDOR SUBGROUP	LOAD # ASSIGNMENT	LOAD COUNT BY VENDOR GP NO	Sum_LOAD COUNT BY VENDOR	LOAD ID1	INTENDED LOAD ID
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		1	5	5	1	1
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		2	5	5	2	2
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		3	5	5	3	3
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		4	5	5	4	4
22161		5	5	5	5	5
22161		5	5	5	5	5
22161		5	5	5	5	5
22161		5	5	5	5	5
22161		5	5	5	5	5
22161		5	5	5	5	5
22161		5	5	5	5	5
22161		5	5	5	5	5
22161		5	5	5	5	5
22161		5	5	5	5	5
22161		5	5	5	5	5
969963		1	1	1	6	1	doesn't match
969963		1	1	1	6	1	doesn't match
969963		1	1	1	6	1	doesn't match
973048		1	1	1	6	1	doesn't match
973048		1	1	1	6	1	doesn't match
973048		1	1	1	6	1	doesn't match
973048		1	1	1	6	1	doesn't match
973048		1	1	1	6	1	doesn't match
973048		1	1	1	6	1	doesn't match
973048		1	1	1	6	1	doesn't match
973048		1	1	1	6	1	doesn't match
973048		1	1	1	6	1	doesn't match
980270		1	1	1	6	1	doesn't match
980270		1	1	1	6	1	doesn't match
733857		1	1	1	6	1	doesn't match
733857		1	1	1	6	1	doesn't match
733857		1	1	1	6	1	doesn't match

[YLYONG · Accepted solution]

@Qiu 

Actually, how did the LOAD ID1 return "1" with below if function?

Thank you

[Qiu]

@YLYONG 

Your 2nd data is different with your first one...
 LOAD ID1 return "1" with below if function since I configure as below.

[binuacs · Accepted solution]

@YLYONG One way of doing this