Trying to look up and filter data

[kingr13]

Hi

I am trying to build a sheet that will lookup a cell for specific text then copy value of a different cell. Also would then need to delete blank rows or one that dont have the specific text i am looking for. Then out of this data I would want to do similar to pick specific items out. I have been able to do the first step via =If function on different sheets. This gives loads of blank rows which i want to get rid of.

This is want I start with to drop into the page

998521 998521 KROK050 998395 BITT ABS 0.35
998521 998521 KROK050 998395 BITT BITT 17.5
998679 998679 REDK050 998652 ABV ABV 4.65
998679 998679 REDK050 998652 AE AE 2.11

and I want to end with KROK050 BITT results only but then on a different sheet use the same to get REDK050 BITT

Thanks
Matt

[Glenn Kennedy]

You can use this, copied across & down:

=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$C$3:$C$20)/(Sheet1!$C$3:$C$20=$A$1),ROWS(A$3:A3))),"")

similar for any other sheets. The value KROK050 can also be hard copied into the formula:

=IFERROR(INDEX(Sheet1!A:A,AGGREGATE(15,6,ROW(Sheet1!$C$3:$C$20)/(Sheet1!$C$3:$C$20="KROK050"),ROWS(A$3:A3))),"")

or to save file size you could put both values into a dropdown in Sheet 2 A1 and select which one you want to look at... two sheets instead of many.

[kingr13]

Thanks that's a great help but would i need to do the same on a different sheet to filter again? I would like the BITT, ABV & AE to be separated after the KROK050 and REDK050 has happened.

[Glenn Kennedy]

I have absolutely no idea what you mean. Using the sheet I posted, SHOW me exactly what you want. Repost it here. The yellow banner (top) tells you how to do that.

[kingr13]

I think i have done it. I added a new sheet then used the same formula to look up the KROK050 data and the REDK050 data for thing 6 and copy thing 7 value. Just did not know if there was a simpler way of doing it

[Glenn Kennedy]

None of these formulae are array formulae. Just use ENTER.

This is fully draggable:

=IFERROR(INDEX(Keg!$C:$H,AGGREGATE(15,6,ROW(Keg!$C$2:$C$9973)/(Keg!$C$2:$C$9973=$A$1),ROWS(A$3:A3)),MATCH(A$3,Keg!$C$1:$H$1,0)),"")

[kingr13]

Is the formulae OK on the other tabs? I have updated what you show and included the "match" part for the original search of the data

[Glenn Kennedy]

I only changed the formulae in the shaded cells in two sheets. the other 2 were fine... apart from the fact that CTRL-SHIFT-ENTER for array entry is NOT needed.

[kingr13]

Is there a way for these formulae to use partial text, So the * would normally do this in some functions

[Glenn Kennedy]

Once again... Show me on a sample file.

[kingr13]

So it currently uses cell A1 as the reference point to search KROK050 but what if I also want to find KROK100? I have used the * as "partial text*" in a formula before but it is not the best in IF.

Would =IFERROR(INDEX(Keg!$C:$H,AGGREGATE(15,6,ROW(Keg!$C$2:$C$9973)/(Keg!$C$2:$C$9973="KROK*"),ROWS(A$3:A3)),MATCH(A$3,Keg!$C$1:$H$1,0)),"") work?

[Glenn Kennedy]

OK. Well, it depends exactly on your set up... one way would be to use this:

=IFERROR(INDEX(Keg!$A:$H,AGGREGATE(15,6,ROW(Keg!$C$2:$C$10000)/(Keg!$C$2:$C$10000={"KROK050","KROK100"}),ROWS(A$3:A3)),MATCH(KROK050!A$3,Keg!$A$1:$H$1,0)),"")

But it could get messy if there were lots of terms. In which case something else would be needed.

[kingr13]

thanks some to be getting it all sorted now with your help

[Glenn Kennedy]

So... if you wanted anything that begins with KROK... and there were dozens of 'em...

=IFERROR(INDEX(Keg!$A:$H,AGGREGATE(15,6,ROW(Keg!$C$2:$C$10000)/(--ISNUMBER(SEARCH("KROK",Keg!$C$2:$C$10000))),ROWS(A$3:A3)),MATCH(KROK050!A$3,Keg!$A$1:$H$1,0)),"")

see file.

[kingr13]

there are not many different types so the previous will work. can I merge two sheets into one. In attached I would like RED COL to show both RED C and RED K. I can do on two different sheets but how can they be on the same one?

[Glenn Kennedy]

The file you uploaded does not contain the original raw data sheet. If you want the col data it would make more sense to do it in 1 step from the raw data, rather than via an intermediate sheet... or am I misunderstanding what you want??!!

[kingr13]

So the raw data I will be using is split but guess could drop into one sheet. Then dont need to get back from two different ones to one when it has been filtered. Attached has how it currently is spread out. So looking to get RED K and C COL on just the one sheet.

[Glenn Kennedy]

Maybe this, copied across and down:

=IFERROR(IFERROR(
INDEX('RED K'!$A:$D,AGGREGATE(15,6,ROW('RED K'!$C$4:$C$9999)/('RED K'!$C$4:$C$9999=$A$1),ROWS(A$3:A3)),MATCH(A$3,'RED K'!$A$3:$D$3,0)),
INDEX('RED C'!$A:$D,AGGREGATE(15,6,ROW('RED C'!$C$4:$C$9999)/('RED C'!$C$4:$C$9999=$A$1),ROWS(A$3:A3)-COUNTIF('RED K'!$C:$C,$A$1)),MATCH(A$3,'RED C'!$A$3:$D$3,0))),"")

[kingr13]

that works but on one sheet it has left 5 blank rows before showing any data. Why would that be?

[JeteMc]

We might be in a better position to help if we could see a sample of the sheet showing the 5 blank rows and if it does not come from the Red C and/or Red K sheets, then the source data from which those rows should be filled.