Formula to get unique data

Hi Alan, have attached the file.

Thank you..

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)

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

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

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?

Formula:

Please Login or Register  to view this content.

copy it in Q5 and drag it down.
Punnam

If this is the technique then put in Q15:

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

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..

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...

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

Thank you

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):

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Let me know if this what you need.
Thanks.

another alternative, if I have understood

Formula:

Please Login or Register  to view this content.

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:

Formula:

Please Login or Register  to view this content.

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

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:

Formula:

Please Login or Register  to view this content.

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

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

Hi Belinda,

Thank you for your help on this, your formula given above works like charm.. taking helper is challenging, i am not sure how can i manage in future expenses or files. I may request you to check below formula which i have gathered over internet bits and pieces.. this will work without helper i guess, but i am not sure how to validate it properly. This formula works to some extent only. If i wanted to apply this formula in same excel sheet, say from Q23:Q30 for "Expenses of Hotel" for various months, results in blank cells. See whether you can help on this!

Formula:

Please Login or Register  to view this content.

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

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)))

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))),"")

Thank you all, that should do..

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