How do I sort lines with the same item and only display the first negative line in a new sheet in Excel

Question

This is my example:

Widget A 150

Widget A 100

Widget A 23

Widget A -6

Widget A -56

Widget B 200

Widget B 75

Widget B -11

Widget B -88

Widget B -130

I want to display this in a new sheet as:

Widget A -6

Widget B -11

In other words, I want to extract the first negative of each item and display in a new sheet. Is there a formula that can do this?

Answer 1

Hi,

If you want to include the date in your final data, then use this formula:

=LET(tx, A2:A11, rg, --REGEXEXTRACT(tx, "-?\d+(?:[.,]\d+)?"), gb, GROUPBY(REGEXEXTRACT(tx, "^(.*?)(?=\s-?\d+(?:[.,]\d+)?)"), rg, SINGLE,, 0,, rg < 0), VSTACK(HSTACK("Widget","First Negative Value","Date"), HSTACK(gb, XLOOKUP(BYROW(gb, LAMBDA(a, TEXTJOIN(" ",, a))), tx, B2:B11))))

I hope you will respond to this post and tell me whether my formula works for you or not, since you did not provide any response / comment to the first post I made in this thread.

IlirU

Answer 2

Hi @Mike Cardwell,

Use below formula:

=LET(d, A2:A11, n, --REGEXEXTRACT(d, "-?\d+(?:[.,]\d+)?"), GROUPBY(REGEXEXTRACT(d, "^(.*?)(?=\s-?\d+(?:[.,]\d+)?)"), n, SINGLE,, 0,, n < 0))

Cheers,

IlirU

Answer 3

Hi,

In cell D2, enter this formula

=GROUPBY(A2:A11,B2:B11,SINGLE,,0,,B2:B11<0)

Hope this helps.

Answer 4

Hi @Mike Cardwell,

Thank you for posting your question in the Microsoft Q&A forum.

According to your concern, you can try using one of these formulas:

Option 1: One spill formula only

I import your example data in range A2:B11. Then in cell D2, use this formula:

=LET(items,$A$2:$A$11,totals,$B$2:$B$11,u,UNIQUE(items),firstNeg,MAP(u,LAMBDA(x,XLOOKUP(1,(items=x)*(totals<0),totals,""))),HSTACK(u,firstNeg))

Option 2: If you prefer separate columns for simpler reading

• For listing unique widgets: in cell G2, use this formula =UNIQUE(A2:A11)
• For returning first negative for each widget: in cell H2, use =XLOOKUP(1,(A$2:A$11=G2)*(B$2:B$11<0),B$2:B$11,"") then dragging the formula for other widget

I hope this information helps. Please try the steps and let me know whether they resolve your concern. If you have further questions or need more clarifications, please don’t hesitate to reach out again on this post and I will be happy to assist you further.

Thank you for your understanding and cooperation. I'm looking forward to your reply.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".       

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread

Answer 5

To achieve the desired result of extracting the first negative value for each unique item in Excel, you can use a combination of formulas. Here's a step-by-step guide:

1. Assuming your data is in columns A and B (with items in column A and values in column B), you can create a new sheet to display the results.
2. In the new sheet, you can list the unique items from your original data. You can do this manually or by using the UNIQUE function if you have Excel 365. For example, in cell A1 of the new sheet, you can enter:

      =UNIQUE(Sheet1!A:A)
   

   (Replace Sheet1 with the actual name of your original data sheet.)
3. Next to each unique item, use the following formula to find the first negative value:

      =MINIFS(Sheet1!B:B, Sheet1!A:A, A1, Sheet1!B:B, "<0")
   

   Place this formula in cell B1 of the new sheet and drag it down alongside your unique items. This formula will return the minimum (first) negative value for each item listed in column A.
4. Filter out errors (if any) that occur when there are no negative values for an item by wrapping the formula in an IFERROR function:

      =IFERROR(MINIFS(Sheet1!B:B, Sheet1!A:A, A1, Sheet1!B:B, "<0"), "")
   

This will give you the first negative value for each item in the new sheet as you specified.

---

References:

• Sort Worksheets Alphanumerically by Name