0

I have a large set of data that has clients name, product type and then premium. I want to group all the clients products together and then delete any clients where their total premium is more than 10k. Is this possible? I have used subtotal which shows me the total premium for each client but how do I remove the ones over 10k?

Thanks

Subtotals have given me the total premium for each client but I don’t know how to delete any clients over 10k without going through each client individually and there are over 4k clients

Improve this question
1
  • Hi, what’s the version of Excel you’re using? Commented Oct 22, 2024 at 15:09

2 Answers 2

Reset to default
0

While the look of your data in unclear, the general approach to solve your task is here.

You need to use an additional column to put there a checkmark.

  1. Add an additional column.
  2. Using autofilter, filter rich clients.
  3. Select cells in additional column and put checkmark there (any value).
  4. Clear autofilter.
  5. Sort the range by the checkmark column.
  6. Select the marked range then select rows (Shift+Space).
  7. Delete selected rows.

enter image description here

May be, you need to preserve the initial order of rows. In this case, use one more column and put there the numeration prior starting the procedure above. At the end, sort the range by this numeration and delete the column.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

To keep filtered records according to the criteria:

  1. Back the current file

  2. Add another sheet and enter in the first cell (please adjust sheet name Sheet1 and the range):

=LET(last_cell, XLOOKUP(TRUE, Sheet1!C:C <> "", Sheet1!C:C, , , -1),
data, Sheet1!A2:last_cell, header, Sheet1!A1:C1, threshold, 10 * 1000,
VSTACK(header,
FILTER(data, SUMIFS(INDEX(data, , 3), INDEX(data, , 1), INDEX(data, , 1)) < threshold)))

Filtered results

  1. Select all data in the new sheet and copy

  2. Add another sheet and select Edit > Paste Special… > Values

  3. Delete the other sheets

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.