Marking values presented more that once in filter window
While working with purchase specifications, the same positions in differens rows often appear with different quantities in each row which should be summed up for sending a request. It would be very convenient to have values corresponding with more then one rows marked by font, color, etc. in filter window…
Kenneth Barber commented
From what I gather, this is your request: get the result of SUBTOTAL for each unique item for each column. The "for each column" requirement makes the use of helper columns absurd, since you would need 1 for each column. Your approach: mark the duplicates so you know what to delete after you get the SUBTOTAL result.
I still say that PivotTables are the best solution for you. Rather than deleting duplicates, you use your "big table" as a source table. From there, the PivotTables can count the number of occurrences of each item in any column. The advantage of leaving your "big table" alone is that you can modify the data in it and have the PivotTables reflect the changes.
This is obviously hard to visualize without an example. Play with the file linked below and see if it's something like what you want.
Well, Ash. Let we have a specification in which the same items could be repeated from part to part (because they are required for several different units or branches, etc.) in various quantities. However, we will not send such requests to our suppliers since a) they will be rather big, and b) it is impolite. So, we have to prepare another, shortened version of the specification where each item appears only once and represents its full quantity. The issue is: how to identify which positions are present more than in one row?
The clearest and the most natural way is to mark such positions just in the filter window: for example, by bold font. It would let consequently apply filtering just to "multi-row" positions and to copy the result of SUBTOTAL() to the first filtered row then deleting the others. Indicating the number of the appropriate rows would be useful too.
Well, there is a "roundabout way" to do it using conditional formatting. Why is it bad? The reason is: you cannot apply both color and text filtering to the same column at the same time. When you apply conditional formatting to the appropriate column, e. g. "Model ID", ALL the sets of repeated positions receive the same color. In order to keep them filtered, you should mark these positions by color in another column (or to add one more column just for this purpose), then apply filtering by color to this "another" column, then return to the column interesting for us and filter it by text… Moreover, all the process should be repeated several times since "Model ID" is not the only column to be checked. OCR mistakes occur rather often, especially taking into account that in Latin and Russian alphabet 11 pairs of letters looks absolutely equally but are different in Unicode, and 0 may be mixed up with both Os (Latin and Russian), and Russian Z ("З") is similar to "three"… So sometimes only "Initial PrIce" column could help though it requires just a human approach and cannot be made automatically.
In these conditions, my proposal provides much more convenience. I prepare an illustration for it, see https://scontent-frt3-1.xx.fbcdn.net/hphotos-xtp1/v/t1.0-9/12688211_10153307046081850_7787787511051127552_n.jpg?oh=689877c9310c3e19257bfef0bbaddcf2&oe=5767A371
I'm having trouble understanding this idea clearly to see whether Kenneth's input (which seem like the best solutions) are overkill or not. Seems like you have a range of cells where several columns together identify a unique product and you want to be able to operate on them as a group - change fonts/colors/summarize, etc.?
Is it possible for you to describe in more details?
Ashvini Sharma [Microsoft Excel]
The way proposed by Kenneth Barber is not so convenient and simple as proposed by me. You may need to check several columns (product name, part number, etc.) for duplicate values so you will have to apply conditional formatting, i. e. to make changes in a big part of specification. If my proposal is implemented, no changes should be made at all to see whether duplicates are present and what are they…
Kenneth Barber commented
To mark duplicate values in a column, go Home tab, Conditional Formatting, Highlight Cell Rules, Duplicate Values. Then use the SUBTOTAL function to get a summation of the whole column to be summed. When you apply a filter, SUBTOTAL will ignore filtered out results (unlike SUM).
A better idea still is to put your data in a table first (Insert tab, Table). Then click in the table, go to the Design tab, and check "Total Row". This adds a total row at the bottom of your table. Filter as you normally would.
If it is not absolutely crucial that you mark values by colour, you can do your summation using SUMIFS. For example, SUMIFS(A:A,"Bob",B:B) reads as "sum the values in column A where the corresponding value in column B is 'Bob'". In other words, a conditional summation.
You might also want to look into PivotTables, which give you the most flexibility in summations.
Simply put, there are lots of summation options out there.
As for counting the number of same values, use SUBTOTAL(2,), COUNTIFS, or PivotTables. It's a similar deal.
Well, in addition to the mentioned, it would be better to add number of rows with the same value to the end of the appropriate item in filter list, e. g. "Canon EOS 750D (3)", "Nikon D7200 (2)"…