Formula to get unique data

[Kishore K]

I have data in cell Q5:Q9, and amounts in P5:P9, where Q colum data is default and will input in same like that.
P Q
5 250 Expenses Airfare Aug-19
6 500 Expenses Airfare Jul-19
7 250 Expenses Airfare Jun-19
8 500 Expenses Airfare May-19
9 250 Expenses Airfare May-19

Now, I need a formula in Q10:Q13, Single Text from Q5:Q9 if the amounts is related to same month. For Ex: Q13 will be "Expenses Airfare May-19" and P13 will be sum of 750. and the formula has to continue to next rows if there are other months too.
P Q
10​ 250 Expenses Airfare Aug-19
11​ 500 Expenses Airfare Jul-19
12​ 250 Expenses Airfare Jun-19
13​ 750 Expenses Airfare May-19

[alansidman]

Read the yellow banner at the top of this page on how to attach a file.

[Kishore K]

Hi Alan, have attached the file.

Thank you..

[davsth]

Assuming column P is not relevant and your attachment does not match with your description
if the dates in the cell you are matching against are dates
=SUMIF($R$5:$R$14,"*"&TEXT(T6,"mmm-yy"),$Q$5:$Q$14) where t6 is the date

or if text
=SUMIF($R$5:$R$14,"*"&RIGHT(t6,6),$Q$5:$Q$14)

[Kishore K]

Hi, sorry for that confusion in excel file. i have tried to put the formula but i am getting blank data.

[Limor_OP]

Davsth - Which cell has this formula, and what would be on column T that you refer to in your formula?

[Limor_OP]

Not really clear what you are trying to achieve.
In Q15 you are expecting to see "Expenses Airfare May-19" because the value in B15 is equal to the value in P8 and then you want to return the same text that it has?
You have another 500 in P5 with another text. So you want the formula to search for the last matching value and return its text in column Q?

[Kishore K]

Hi, let me explain this. Data in Q5:Q14 is constant with text, expense data with various months, as well in P5:P15 with amounts. the Expense months may vary or similar months. now i need a formula in Q15, like, cost for Aug-19 is $500 and Q15 will be "Expenses Airfare Aug-19" and in P15 $500. Wheres as for repeated months for ex, in Q18 i will need data clubbing of May-19 as a whole and sum of May-19 in P18 $1300 ($500+350+450). and the Q19 has to be Oct-19 data and sum of $1,200(550+650) in P19.. So on..

Hope i have put it fine..

[Limor_OP]

Hi Kishore K,
Thank you for the clarification. Now it is well understood, I will think about a proper solution, or maybe someone here will figure this out before me...

[Kishore K]

Glad you got it.. hope this can have a better solution..

Thank you

[Limor_OP]

Hi,
I managed to do that only with a helper though I'm sure it is possible without (with frequency function or something like that).

So the helper in U5 and down would retrieve unique values, and leave blank cells for the duplicate lines (I couldnt find a way to overcome the blank cells):

=IF(OR(COUNTIF($U$4:U4,INDEX($Q$1:$Q$14,SMALL(IF(Q5=$Q$1:$Q$14,ROW($Q$1:$Q$14),ROW(Q5)),1),1))>0,U4=INDEX($Q$1:$Q$14,SMALL(IF(Q5=$Q$1:$Q$14,ROW($Q$1:$Q$14),ROW(Q5)),1),1)),"",INDEX($Q$1:$Q$14,SMALL(IF(Q5=$Q$1:$Q$14,ROW($Q$1:$Q$14),ROW(Q5)),1),1))

Then you can apply in Q15 and down the following formula that skips the blanks:

=IFERROR(INDEX($Q$1:$Q$14,SMALL(IF($U$5:$U$13<>"",ROW($U$5:$U$13)),ROWS($U$5:U5)),1),"")

Let me know if this what you need.
Thanks.

[Punnam]

"Expenses Airfare"&" "&TEXT(T5,"mmm-yy")

copy it in Q5 and drag it down.
Punnam

[Limor_OP]

If this is the technique then put in Q15:

=INDEX($Q$5:Q14,MATCH(2,INDEX(1/($P$5:P14=P15),)),1)

[XLent]

another alternative, if I have understood

Q15:

=INDEX($Q:$Q,AGGREGATE(15,6,ROW($Q$5:$Q$14)/(SUMIF($Q$5:$Q$14,$Q$5:$Q$14,$P$5:$P$14)=$P15)/(MATCH($Q$5:$Q$14,$Q$5:$Q$14,0)=(ROW($Q$5:$Q$14)-ROW($Q$5)+1)),COUNTIF($P$15:$P15,$P15)))

copied down

the above assumes monthly totals could repeat, e.g. if P14 were 550 rather than 350 -- in which case the above would assign first found, then second found etc
if the monthly totals are guaranteed to be unique, then:

Q15:

=INDEX($Q$5:$Q$14,MATCH($P15,INDEX(SUMIF($Q$5:$Q$14,$Q$5:$Q$14,$P$5:$P$14),0),0))

copied down

[Limor_OP]

Hi Xlent,

Q15 gives a zero. Can you please upload it in excel? I will be happy to see another solution.
Just to make it clear - the purpose is that the below duplicate lines should appear only 1 time (the lines with the text containing May/Oct/Sep).
then - a sumif function will be effected in column P

Thanks.

Expenses Airfare Aug-19
Expenses Airfare Jul-19
Expenses Airfare Jun-19
Expenses Airfare May-19
Expenses Airfare May-19
Expenses Airfare May-19
Expenses Airfare Oct-19
Expenses Airfare Oct-19
Expenses Airfare Sep-19
Expenses Airfare Sep-19

[Kishore K]

Thank you all, that should do..

[Kishore K]

Hi,

Please help with a formula for below.
Having data in A5:C12. Where I wanted the data in A16 with "Sales & Department &month" data by eliminating the blanks in between for various months

MONTH DEPT. SALES
Jan-20 12110 500
13110 300
14110
15110 100
Feb-20 13110 200
12110
15110 300


SUMMARY
SALES 12110 JAN-20 500
SALES 13110 JAN-20 300
SALES 15110 JAN-20 100
SALES 13110 FEB-20 200
SALES 15110 FEB-20 300

[XLent]

I had assumed the values in P15 onwards existed to which the OP was trying to assign the correct label, if that's not the case, i.e. generate unique aggregates of rows 5:14 then:

Q15: =INDEX($Q$5:$Q$14,AGGREGATE(15,6,(1+ROW($Q$5:$Q$14)-ROW($Q$5))/(MATCH($Q$5:$Q$14,$Q$5:$Q$14,0)=(1+ROW($Q$5:$Q$14)-ROW($Q$5))),ROWS(Q$15:Q15)))

P15: =SUMIF($Q$5:$Q$14,$Q15,$P$5:$P$14)

copied down

but a Pivot Table would make more sense if that's the case.

[Kishore K]

Hi,

Thank you with the formula, this is working fine.. but if we extend the formula in same excel sheet in Q column, the formula is throwing error. I wanted to apply the formula Q23:Q30 (this may go on to Q50 depends on number of expenses). please help

[Limor_OP]

OK. This is it.
Thank you very much for sharing your knowledge!

[Limor_OP]

Hi Kisore,

Xlent's formula is working very well without a helper , but if you want to apply it on a new segment you will have to change the range in bold to reflect the new range.
Does the extent of your segment going to be a lot wider than in your example? (the Maximum range in the below adjusted formula is 24 lines)

I added lines to your example just to test it.

In Q36
=INDEX($Q$23:$Q$33,AGGREGATE(15,6,(1+ROW($R$1:$R$24)-ROW($R$1))/(MATCH($Q$23:$Q$33,$Q$23:$Q$33,0)=(1+ROW($R$1:$R$24)-ROW($R$1))),ROWS(Q$23:Q23)))

[Limor_OP]

Regarding the formula you found on the net, see below how it is customized for your new segment.

It is less practical as I had to calculate what should be the number of rows in the array (ROW(Q$5:Q$12)-4) in order for it to match the exact number of rows in the data array (MATCH(Q$23:Q$30,Q$23:Q$30,)). If the ranges do not match in quantity it returns N/A

So the ranges will look like that - 8 characters in each array:
{1;1;3;4;5;5;7;7}={1;2;3;4;5;6;7;8}

Unless you or someone here finds a way to overcome that - and assuming your ranges are changing - perhaps this formula is less recommended.

Q31 and down (Bold needs to be adjusted:
=IFERROR(INDEX(Q$23:Q$30,SMALL(IF(MATCH(Q$23:Q$30,Q$23:Q$30,)=ROW(Q$5:Q$12)-4,ROW(Q$5:Q$12)-4),ROW(A1))),"")

[bebo021999]

Try
Q15=Q5
Q16:

=IFERROR(INDEX($Q$5:$Q$14,MATCH(0,COUNTIF($Q$15:Q15,$Q$5:$Q$14),0)),"")

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
Drag down
Similar to next section:
Q31=Q23
Q32:

=IFERROR(INDEX($Q$23:$Q$30,MATCH(0,COUNTIF($Q$31:Q31,$Q$23:$Q$30),0)),"")

Also Control-Shift-Enter
Drag down

[alansidman]

OP has opened a new thread on this latest issue at https://www.excelforum.com/excel-for...713-sales.html. Am closing this thread as OP has indicated that it has been resolved.