I'm going to pull my remaining hair out soon about this. Any help will be
gratefully received. Here we go:

I'm trying to run a VLOOKUP to find the manning of a vehicle which will
appear 4 times under the same name and under different times.

A B C D

1 F101 0630-1830 Smith COVERED
2 F101 0630-1830 Jones COVERED
3 F101 1830-0630 Bloggs COVERED
4 F101 1830-0630 Uncovered UNCOVERED


This is a few columns of a huge package od data that i have to filter down
when the information is automatically refreshing and the "F101" bit changes
its position depending upon the day.

I have sorted that out but i have tried to use VLOOKUP to look for just the
information that i want but it will only look at the very first line and i
need it to give me all four. I have managed to change it so that i only have
to look for one time (0630-1830) but i still just get a duplicate result.

What i receive is

A B C D

1 F101 0630-1830 Smith COVERED
2 F101 0630-1830 Smith COVERED
3 F101 0630-1830 Smith COVERED
4 F101 0630-1830 Smith COVERED


The information that i want will always be on the line below the first
result if that helps with this.

HELP.

One easy play which delivers it ..

Source data assumed in Sheet1, cols A to D,
data from row2 down, with key col = col A

In another sheet,
Input for the key col will be done in A2, eg: F101

In C2:
=IF($A$2="","",IF(Sheet1!A2=$A$2,ROW(),""))

In D2:
=IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1))))
Copy D2 across to G2. Select C2:D2, copy down to cover the max expected
extent of data in Sheet1, eg down to D200? Mininize/hide col C. Cols D to G
will return the results that you seek, all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"pete8125" wrote:
I'm going to pull my remaining hair out soon about this. Any help will be
gratefully received. Here we go:

I'm trying to run a VLOOKUP to find the manning of a vehicle which will
appear 4 times under the same name and under different times.

A B C D

1 F101 0630-1830 Smith COVERED
2 F101 0630-1830 Jones COVERED
3 F101 1830-0630 Bloggs COVERED
4 F101 1830-0630 Uncovered UNCOVERED


This is a few columns of a huge package od data that i have to filter down
when the information is automatically refreshing and the "F101" bit changes
its position depending upon the day.

I have sorted that out but i have tried to use VLOOKUP to look for just the
information that i want but it will only look at the very first line and i
need it to give me all four. I have managed to change it so that i only have
to look for one time (0630-1830) but i still just get a duplicate result.

What i receive is

A B C D

1 F101 0630-1830 Smith COVERED
2 F101 0630-1830 Smith COVERED
3 F101 0630-1830 Smith COVERED
4 F101 0630-1830 Smith COVERED


The information that i want will always be on the line below the first
result if that helps with this

Max

Thanks for that, i tried it and it worked perfectly. The only problem is
thay "F101" was an example and i have about 150 fixed call signs (eg F101)
and i couldn't figure this out for multiple callsigns.

I have tried to list the various callsigns in col A but if i listed a second
one it removed the infor of the first.

is there any way of listing the various

"Max" wrote:

One easy play which delivers it ..

Source data assumed in Sheet1, cols A to D,
data from row2 down, with key col = col A

In another sheet,
Input for the key col will be done in A2, eg: F101

In C2:
=IF($A$2="","",IF(Sheet1!A2=$A$2,ROW(),""))

In D2:
=IF(ROWS($1:1)COUNT($C:$C),"",INDEX(Sheet1!A:A,SM ALL($C:$C,ROWS($1:1))))
Copy D2 across to G2. Select C2:D2, copy down to cover the max expected
extent of data in Sheet1, eg down to D200? Mininize/hide col C. Cols D to G
will return the results that you seek, all neatly packed at the top.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---
"pete8125" wrote:
I'm going to pull my remaining hair out soon about this. Any help will be
gratefully received. Here we go:

I'm trying to run a VLOOKUP to find the manning of a vehicle which will
appear 4 times under the same name and under different times.

A B C D

1 F101 0630-1830 Smith COVERED
2 F101 0630-1830 Jones COVERED
3 F101 1830-0630 Bloggs COVERED
4 F101 1830-0630 Uncovered UNCOVERED


This is a few columns of a huge package od data that i have to filter down
when the information is automatically refreshing and the "F101" bit changes
its position depending upon the day.

I have sorted that out but i have tried to use VLOOKUP to look for just the
information that i want but it will only look at the very first line and i
need it to give me all four. I have managed to change it so that i only have
to look for one time (0630-1830) but i still just get a duplicate result.

What i receive is

A B C D

1 F101 0630-1830 Smith COVERED
2 F101 0630-1830 Smith COVERED
3 F101 0630-1830 Smith COVERED
4 F101 0630-1830 Smith COVERED


The information that i want will always be on the line below the first
result if that helps with this

Thanks for that, i tried it and it worked perfectly.

That's good. Kindly press the Yes button in that response, won't you.

I have tried to list the various callsigns in col A
but if i listed a second one it removed the info of the first

Just make a copy* or two of the entire extract sheet (the "another sheet"),
then it enables you to try different values for the input in A2 in the
copies.
*just hold down CTRL & drag the sheet tab to the left/right to make copies

P/s: You can use either advanced filter uniques, or create a simple pivot
to do a one-time listing of the source col A. For the pivot, just drag n drop
the col header for col A into both the ROW and DATA areas.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:19,500 Files:362 Subscribers:62
xdemechanik
---

Hi,

You may want to try my solution on this page

http://office.microsoft.com/en-us/ex...260381033.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"pete8125" wrote in message
...
I'm going to pull my remaining hair out soon about this. Any help will be
gratefully received. Here we go:

I'm trying to run a VLOOKUP to find the manning of a vehicle which will
appear 4 times under the same name and under different times.

A B C D

1 F101 0630-1830 Smith COVERED
2 F101 0630-1830 Jones COVERED
3 F101 1830-0630 Bloggs COVERED
4 F101 1830-0630 Uncovered UNCOVERED


This is a few columns of a huge package od data that i have to filter down
when the information is automatically refreshing and the "F101" bit
changes
its position depending upon the day.

I have sorted that out but i have tried to use VLOOKUP to look for just
the
information that i want but it will only look at the very first line and i
need it to give me all four. I have managed to change it so that i only
have
to look for one time (0630-1830) but i still just get a duplicate result.

What i receive is

A B C D

1 F101 0630-1830 Smith COVERED
2 F101 0630-1830 Smith COVERED
3 F101 0630-1830 Smith COVERED
4 F101 0630-1830 Smith COVERED


The information that i want will always be on the line below the first
result if that helps with this.

HELP.