• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Index and Match 2 criteria 1 unique value

K

kosmonautas

New Member
Hello,


I am trying to match sales records (day + product) and return client, but some sales overlap.


This returns me the same client for two different entries.


I am sure there is some formula to avoid such thing or to return unique value. Although I found some array formulas on Google wasn't able to apply for my kind of data.


I have attached Excel file to clear things up.


I am using Excel 2010


Thanks for help.


LINK: http://www.2shared.com/file/TPjRoyDd/chandoo_attachment.html
 
Hi ,


I am not sure that one single formula will give what you want ; even if it could , it would be an unwieldy formula ; things can be made easier if you use helper columns. Is this acceptable to you ?


Of course , a single-cell formula such as the following , will do the job :

[pre]
Code: 
=IF(COUNTIFS($A3:$A$8,A3,$B3:$B$8,B3,$C3:$C$8,C3)=1,IF(INDEX(Data_Table_177,MATCH(B3,Product_List_177,0)+1,0) INDEX(Data_Table_177,0,MATCH(A3,Day_List_177,0)+1)=C3,177,IF(INDEX(Data_Table_420,MATCH(B3,Product_List_420,0)+1,0) INDEX(Data_Table_420,0,MATCH(A3,Day_List_420,0)+1)=C3,420,"")),420)
[/pre]
Narayan
 
Additional columns can be used if it helps and makes calculation simpler.


Also if you could upload your sample file with single-cell formula it would be very interesting to see how it works since I cannot manage to apply it with my data. Table part is a bit confusing :).


Thanks in advance.
 
NARAYANK991 I really appreciate your help but now I'm facing another problem.


Since my data is displayed as Table I cannot use Cell reference, so how do I lock my range from #ThisRow till last Row on Table and so on till very last record?
 
Hi ,


I am not very clear on your problem ; what is the meaning of locking your range ? Can you either give more details or upload your actual workbook ? The one you uploaded earlier did not have any table in it.


Narayan
 
I'm back with source file :)


Here's the link:

https://docs.google.com/open?id=0ByqDLydmf7lVX21MMFhIVWJsVFk


Could you please overview "COUNTIFS" column.


The only problem now is that COUNTIFS does not ignore rows before #ThisRow and counts all cells in that column.
 
Hi ,


I am not very conversant with Table formulae and nomenclature ; however , on a standard range , we would use the following formula , which I think , you can use even within the table :

[pre]
Code: 
=COUNTIFS(A2:$A$592,A2,B2:$B$592,B2,C2:$C$592,C2,D2:$D$592,D2)
[/pre]
The above formula , when typed in , in E2 , will be copied by Excel to the remaining relevant cells in column E.


Is this what you are looking for ?


Narayan
 
Yes :), but whenever I add additional data to my table, I'l have to update formula range manually?


You are being very very helpful!!!
 
Hi ,


What I find is the following :


1. The last data row in your workbook is 592 ; here the formula is :

[pre]
Code: 
=COUNTIFS(A592:$A$592,A592,B592:$B$592,B592,C592:$C$592,C592,D592:$D$592,D592)
[/pre]
2. If you place the cursor on row 593 , right click and select "Insert Table row above" , or if you place the cursor on row 592 , right click and select "Insert Table row below" , the formula copied into the blank row is wrong. Also , the remaining formulae in the column , from row 2 till the earlier last row , are not updated to reflect the additional row.


3. If you place the cursor anywhere else in the table , from row 2 till row 591 , and insert a new row , not only does Excel copy the formula into the newly inserted row correctly , it also updates all the remaining formulae to take into account the newly inserted row.


What you can do is always leave a blank row at the end of the table , before your SUMMARY row ; when you wish to insert a new row , place your cursor on this existing blank row , right click and select "Insert Table row above". A new row , blank , will be inserted , and all the formulae will be updated / copied correctly.


Narayan
 
Hello Narayan,


I want to thank you for your help. I successfully applied your solution to my data table :).


Really appreciate your help!
 
You must log in or register to reply here.
Back
Top