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