How to iterate through the number of rows (without using python tool, if possible)?

After using a Summarize tool, my data is like this:

No.	DKACC	Count of Book Num
1	1001	51
1	1002	24
1	1003	7
1	1004	11
2	1005	256
2	1006	34
3	1007	76
3	1008	12
3	1009	2

There are 51 books that have No. 1 and DKACC 1001. There are 24 books that have No. 1 and DKACC 1002, etc.

I want to get the Book Num that have all 4 DKACC and have the same No. 1

Same with No. 2, and No. 3, etc

At first I thought of using the Filter tool to get those that have No=1 and DKACC=1001 then using the Join tool to combine with those that have No=1 and DKACC=1002, etc for all DKACC. But because I have a lot of No and DKACC, I don't want to use so many Filter tools and writing all those filter conditions manually.

Is there a way to do this without using the python tool (and if possible, not using macros as well)?

(I want to avoid using python tool because this workflow will be passed on to someone else who doesn't understand programming at all)

Workflow

Join

Accepted answers

All comments

CoG

At first glance it looks like you would benefit from the Summarize tool, grouping by [No.] and Aggregating with "Sum" on Count of Book Num.

But having read through again, I'm less certain what it is you're looking for. I feel fairly confident you will not need python tool, but I'm not sure what your target output will look like. Can you share the desired output based on the sample input you provided?

CoG

Otherwise, if you are looking for how many books are associated with all [DKACC]'s for a given [No.], then you will not want to use summarized data, because you need each book to be individually identifiable. Based on above data, for example, a max of 7 books could have all four [DKACC]'s for [No.] = 1, since only 7 books [DKACC] = "1003". Filtering and Joining will not provide accurate data in this case.

Qiu

@novalcia
I agree with @CoG , it will be better that you could provide a set of data as input and desired output.

novalcia

Sorry, I didn't explain my question properly. I want to get the [Book Num] of those that have the same [No] and the given [DKACC], not the number of books. I attached a trial data (some [Book Num] may have duplicate [DKACC] but that's okay because the description is different). The expected results is:

No	DKACC	Book Num
1	1001	A1
1	1002	A1
1	1003	A1
1	1004	A1
2	1005	A2
2	1006	A2
1	1001	A4
1	1002	A4
1	1003	A4
1	1004	A4

So from this output I can see that Book with the Number A1 have 4 [DKACC] and [No]=1. And that Book with Number A4 also have all 4 [DKACC] and [No]=1.

In the data I've attached, there's a sheet called "No and DKACC", where the acceptable No and DKACC are shown. In the case of the trial data, Book Num A1, A2, and A4 all meet the "No and DKACC" criteria. But Book Num A3 didn't (because it only have 1007 and 1009), so I don't want A3 to show up in the results table.

Then I guess use the Summarize tool (?), but what I want to get at the end is all the [Book Num] that met the criteria:

Book Num

I hope that makes sense? Sorry English isn't my first language.

trial data.xlsx

CoG

I appreciate the clarification, and it seems like I am still missing a small detail. It seems to me that in the trial data, A3 would be included in the output since the only DKACC's associated with No = 3 are "1007" and "1009", both of which [Book Num] A3 is associated with. In other words, the Book with the Number A3 has all 2 [DKACC] and [No]=3. Is this a misunderstanding? I have a sample of what I think you're looking for that I will include here for reference:

Sample Workflow.yxmd

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15875

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703