ExcelBanter - Lookup Problem

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Lookup Problem (https://www.excelbanter.com/excel-worksheet-functions/52814-lookup-problem.html)

Hello All,

I have an array of data in columns G:K. I need to extract certain
information based on criteria that appears in column I. Easy, right?
The problem is that the data does not appear on every record that needs
to be extracted. Basically, the data that I want to pull has a starting
point (the criteria) and an end point within the column and may appear
multiple times. I only want to pull out the data in the rows from the
start to end point criteria so I guess the lookup needs to be triggered
by the start point. The other issue is that there are multiple end
points. My data appears like this:

G H I J K
1 ASSTCTR FWYDONE 5305TRNMT WPC 257066
2 ASSTCTR FWYDONE INDEXERROR WPC 257066
3 ASSTCTR FWYDONE TRINCSGL WPC 257066
4 ASSTCTR FWYDONE TRINCSGL THB 262943
5 ASSTCTR FWYDONE 4539REORG AKI 238976
6 ASSTCTR FWYDONE INDEXERROR AKI 238976
7 ASSTCTR FWYDONE TRINCSGL AKI 238976
8 ASSTCTR FWYDONE TRINCSGL 3423 241789
9 ASSTCTR FWYDONE TRINCSGL 3423 241789

My lookup is in columns to the right in the same sheet. I want it to
return the values from Row 1-4. In this example, I1 is the starting
point criteria and I5 is the ending point criteria. So I just want
data in rows that starts with the data in I1 until it sees a different
value in column I and continues through the lookup that way. I hope
someone can help!

Cheers,

Steve

--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=480232

Sandy,

Sorry for the confusion. The data set has one startpoint and many
endpoints but they do not overlap. So when the function sees the
startpoint, it will pull the data between it and the first endpoint it
hits and continue to look for the next startpoint. I have 12 different
"triggers" as endpoints. I'm a bit VBA challenged and just starting to
dabble in it so a formula/function would probably be easier for me to
understand.

Thanks for your help!

Cheers,

Steve

--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=480232

Hi Steve,

"triggers" as endpoints. I'm a bit VBA challenged

You and me both <g

I have assmed that the start point is the 5305 in your initial post and
entered the data you supplied in columns F:K starting in Row 2 with headers
in Row 1.
I used a helper column to the right of the data, in ths case Column L

In cell L2 I entered the formula:

=IF(H2="","",IF(H2=5305,"X"))

and in L3:

=IF(OR(AND(L2<"",H3<"",H3<$A$2),H3=$A$2),"X","" )

copied it down and then hid the column.

I then recorded & cleaned up the code to get:

Sub FilterIt()

Dim LastRow As Long

'Find last Row of data
LastRow = Cells(Rows.Count, 6).End(xlUp).Row
'If you have other data in lower Rows change Rows.Count to the _
maximum permissible Row

Application.ScreenUpdating = False

'Unhide column with Xs (Column L =12)
Columns(13).EntireColumn.Hidden = False

'Autofilter, copy & paste data
With Range(Cells(1, 1), Cells(LastRow, 12))
.AutoFilter Field:=12, Criteria1:="X"

Range(Cells(1, 1), Cells(LastRow, 12)). _
SpecialCells(xlCellTypeVisible).Copy _
Destination:=Cells(LastRow + 2, 1)
'Or select a destination of you choice
.AutoFilter
End With

Application.CutCopyMode = False

Columns(13).EntireColumn.Hidden = True

Application.ScreenUpdating = True

End Sub

However, I still think that some of the clever people around here could do
it using only worksheet finctions and I KNOW that they could write better
code so watch this space. <g

If you need any help with what I have said then do post back.

--
HTH

Sandy

Replace@mailinator with @tiscali.co.uk

"SteveG" wrote in
message ...

Sandy,

Sorry for the confusion. The data set has one startpoint and many
endpoints but they do not overlap. So when the function sees the
startpoint, it will pull the data between it and the first endpoint it
hits and continue to look for the next startpoint. I have 12 different
"triggers" as endpoints. I'm a bit VBA challenged and just starting to
dabble in it so a formula/function would probably be easier for me to
understand.

Thanks for your help!

Cheers,

Steve

--
SteveG
------------------------------------------------------------------------
SteveG's Profile:
http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=480232

Sandy,

Thanks for the info. I'll let you know how I make out.

Cheers,

Steve

--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=480232