Re: 30USD Macro to Count, Update, Search Table
in my first sheet (Ports) all information will come from, then in Sum sheet is a summary from this Ports sheet table.
For example, in Sum sheet table, there is a list of ID (2000,2001,2002, etc), then for each field below will do some computation, lets filter ID 2000 in first sheet (Ports)
- the Cap field will only count the no of ID exist in the with respect to that ID, so ID 2000 will have 16, ID 2001 have 16 also and so on to the other ID, when you filter it out.
- the CapID field will only count the number of listed in RName field with respect to ID itself, when we filter ID 2000 in (ID field) you'll notice that it has 4 items in RName field (M_2000_1, M_2000_2, 2005_1 and 2005_2) but we only needed the ID that has the same as what we look which is ID 2000 (disregard the other characters), that is ID 2000 which is counted as 2 as shown in the table (Sum Sheet)
- the SumCap is much easier, count only the number of listed names exist in RName field, which is counted as 4 (M_2000_1, M_2000_2, 2005_1 and 2005_2)
- the NODepID field will only count the unique ID in RName field but not included the ID itself, as you filter ID 2000 in ID field you'll find that it has 4 item listed in RName field as M_2000_1, M_2000_2, 2005_1 and 2005_2, but should disregard other characters so we come up with duplicate ID for ID 2000 and ID 2005, since ID itself is not included (as we filter only ID 2000) it will counted to only 1 which is ID 2005 as a unique ID.
- in IDs field should show that unique IDs that was mentioned in NoDepID field which is ID 2005 for filtering ID 2000.
The output is on the Sum sheet which is the data in fields "Cap", "CapID", "SumCap", "NoDepID" and "IDs". The only data is on the field ID (2000, 2001, 2002, et). The source data is all in "Ports" sheet that will be using in gathering this information needed for each fields in the "Sum" sheet.
The other sheets,
- SearchID are only be used as a filter in "Sum" sheet, showing the row table with respect to search ID and the IDs in the "IDs" field
- For ID 2000, should show ID 2000 as well as ID 2005
- SearchPorts is the same criteria as "SearchID" sheet but the table source is come from "Ports" sheet.
Hope you'll be able to find a better solution for this.