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

Formula For loookup along with filtered data

T

TPR

Member
Dear Friends,

Pls go thru the uploaded file. A & B columns are my Data.
G is a criteria. I need a formula that will give me the desired results against a Group. The more new suppliers will be added the more names will be added in column G.

G1 can be a drop down list of Groups.

Thanks in Advance

Regards
TPR
 

Attachments

  • formula_filtered.xlsx
    9.6 KB · Views: 9
you can do little modificaton in this formula for your requirement and paste it h6 to h 11 as "The more new suppliers will be added the more names will be added in column G." like below.

=INDEX($B$2:$B$65000,MATCH(0,IF($A$2:$A$65000=$F$2,COUNTIF($H$1:H1,$B$2:$B$65000)),0))
 
Dear Friends,

Kindly go thru the attached file, now I don't want to filter the data. I want the all data even duplicate also. Yellow highlighted are desired result. Pls help me providing the formula.

Thanks in advance,
Regards
TPR
 

Attachments

  • non -filtered data.xlsx
    10.6 KB · Views: 2
TPR said:
Dear Friends,

Kindly go thru the attached file, now I don't want to filter the data. I want the all data even duplicate also. Yellow highlighted are desired result. Pls help me providing the formula.

Thanks in advance,
Regards
TPR
Click to expand...

A formula solution with helper columns way.

1] In "helper1" Col I, I2 copied down :

=IF(F2<>"",1,I1+1)

2] In "helper2" Col J, J2 copied down :

=IF(COUNTIF(A$1:A$100,LOOKUP("zz",F$2:F2))>=I2,LOOKUP("zz",F$2:F2),"")

3] In "Supplier" Col G, G2 copied down :

=IF(J2="","",INDEX(B$1:B$100,AGGREGATE(15,6,ROW(A$1:A$100)/(A$1:A$100=LOOKUP("zz",F$2:F2)),COUNTIF(J$2:J2,J2))))

Regards
Bosco
 

Attachments

  • non -filtered data(1).xlsx
    14 KB · Views: 3
Dear Sir,

My data are in column A & B, & in original file F2 is data validated & like column B data there are 5 more columns that also would be available at the same time, then should I have to add five more helper columns to get the data ?

If there is little easy way out pls help.

Regards
TPR
 
TPR said:
Dear Sir,

My data are in column A & B, & in original file F2 is data validated & like column B data there are 5 more columns that also would be available at the same time, then should I have to add five more helper columns to get the data ?

If there is little easy way out pls help.

Regards
TPR
Click to expand...
It is hard to understand in your statement, please upload your file together with the 5 more columns and your expected output.

Regards
Bosco
 
Pls go thru Uploaded file. "supplier" sheet is data. in sheet2 I have entered the following formula in B2:
{=IFERROR(INDEX(supplier!$B$2:$B$152,MATCH(0,IF(supplier!$A$2:$A$152=$A$1,COUNTIF($B$1:B1,supplier!$B$2:$B$152)),0)),"")}

other cells are carrying same formula only cell values are changed. Indigo highlighted area is carrying the formula & present result. Green highlighted are my expected output.

Pls help.
Regards
TPR
 

Attachments

  • non -filtered data.xlsx
    13.1 KB · Views: 3
TPR said:
Pls go thru Uploaded file. "supplier" sheet is data. in sheet2 I have entered the following formula in B2:
{=IFERROR(INDEX(supplier!$B$2:$B$152,MATCH(0,IF(supplier!$A$2:$A$152=$A$1,COUNTIF($B$1:B1,supplier!$B$2:$B$152)),0)),"")}

other cells are carrying same formula only cell values are changed. Indigo highlighted area is carrying the formula & present result. Green highlighted are my expected output.

Pls help.
Regards
TPR
Click to expand...

1] Your 2nd file Output table layout is difference from post #1 file.

2] Formula to 2nd file Output table.

2.1] In "Sheet2" B11 (green area), formula copied across and down :

=IFERROR(INDEX(supplier!$A$1:$F$200,AGGREGATE(15,6,ROW(supplier!$A$1:$F$200)/(supplier!$A$1:$A$200=$A$1),ROWS($1:1)),MATCH(B$1,supplier!$A$1:$F$1,0)),"")

2.2] See revised attachment.

Regards
Bosco
 

Attachments

  • non -filtered data(2).xlsx
    15 KB · Views: 6
Last edited:
Just awesome !!!!
Lil curious to know after AGGREGATE, Why 15 & 6, pls tell me to learn.
Regards
TPR
 
Hi Bosco,
one issue.
Suppose I have same type of data in 2 more different sheets ("supplier2" & "supplier3") with different values but 90% foods will remain same, if not i shall add to data validation also in cell A1.
Now if I want to add another data validated cell in "sheet2" A2 cell with sheet names, how the formula would be changed?
Pls help to give me this solution.
Thanks in advance.
Regards
TPR
 
TPR said:
Hi Bosco,
one issue.
Suppose I have same type of data in 2 more different sheets ("supplier2" & "supplier3") with different values but 90% foods will remain same, if not i shall add to data validation also in cell A1.
Now if I want to add another data validated cell in "sheet2" A2 cell with sheet names, how the formula would be changed?
Pls help to give me this solution.
Thanks in advance.
Regards
TPR
Click to expand...
Please upload your file with example data together with your expected output.

Regards
Bosco
 
bosco_yip said:
1] Your 2nd file Output table layout is difference from post #1 file.

2] Formula to 2nd file Output table.

2.1] In "Sheet2" B11 (green area), formula copied across and down :

=IFERROR(INDEX(supplier!$A$1:$F$200,AGGREGATE(15,6,ROW(supplier!$A$1:$F$200)/(supplier!$A$1:$A$200=$A$1),ROWS($1:1)),MATCH(B$1,supplier!$A$1:$F$1,0)),"")

2.2] See revised attachment.

Regards
Bosco
Click to expand...
Dear Sir,
error is coming if I select vegetable from drop down list.
Pls check the yellow highlighted portion. I want if supplier name is same but address or any other column data is different, then that should be repeated. But here it is not happening. Samar's 2nd address is not showing, Ramu & Sidhu's addresses are showing wrong.
Pls help.
Thanks in advance.
Regards
TPR
 
You must log in or register to reply here.
Back
Top