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)
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?
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.
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 |
A1 |
A2 |
A4 |
I hope that makes sense? Sorry English isn't my first language.
Hi,
Why is A2 included in your example answer? It looks to me like it should not be...
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: