Lookup Problem

[SteveG]

Hello All,

I have an array of data in columns G:K. I need to extract certai
information based on criteria that appears in column I. Easy, right?
The problem is that the data does not appear on every record that need
to be extracted. Basically, the data that I want to pull has a startin
point (the criteria) and an end point within the column and may appea
multiple times. I only want to pull out the data in the rows from th
start to end point criteria so I guess the lookup needs to be triggere
by the start point. The other issue is that there are multiple en
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 t
return the values from Row 1-4. In this example, I1 is the startin
point criteria and I5 is the ending point criteria. So I just wan
data in rows that starts with the data in I1 until it sees a differen
value in column I and continues through the lookup that way. I hop
someone can help!

Cheers,

Stev

 

[Sandy Mann]

Steve,

I find your explanation a bit confusing. You appear to be saying that you
may have only one staring point but many end points, is this correct or do
you mean many (different) start/endpoints? If so can start/ end point
overlap as in:

Start point 1
Start point 2
End point 2
Start point 3
End point 1
End point 3

Would you then want all of the data in Start/End 1 to include or exclude the
data in Start/End 2 and part of the data from Start/End 3?

Also you posted to worksheets.functions - it may be possible to do using
functions but would a VBA answer suffice?
--
Regards


Sandy
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[SteveG]

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

 

[Sandy Mann]

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
(e-mail address removed)
Replace@mailinator with @tiscali.co.uk

 

[SteveG]

Sandy,

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

Cheers,

Steve