Well, this is getting kinda spooky.
The weird thing is that just like yesterday, it is working for the last 2 (
in rows 679 and 744) but not the first 2 (in rows 413 and 645).
Because of the same problems as yesterday, I thought maybe something was
wrong with the original formatted data on the dataprior sheet, so I formatted
those cells all the same, but no change in the result.
My data is in rows 3:1000 on the data prior and data sheet. Here are the
formulas I'm using on the check sheet:
In A3, dragged to A1000:
=IF('Data Prior'!H3="","",IF('Data Prior'!H3="No Longer Here",ROW(),""))
In B3, dragged to B1000:
=IF(ROW(A3)COUNT(A:A),"",INDEX('Data Prior'!$A$3:$A$1000,SMALL(A:A,ROW())))
Also, this might be the weird part. The references for the data sheet (new
employees) is doing the same thing. Giving the correct results for the last 2
(in rows 595 and 608), but not in rows 549 and 514.
I entered these formulas on the check sheet in columns C & D, with these
dragged to 1000:
In C3, =IF(Data!H3="","",IF(Data!H3="New Employee",ROW(),""))
In D3, =IF(ROW(C3)COUNT(C:C),"",INDEX(Data!$A$3:$A$1000, SMALL(C:C,ROW())))
I'm perplexed as to why it works great with the upper range data, but not
the lower range. Also, it's just a cooincidence that both data and data prior
have 4 incidents. Each could be different on different weeks of data download.
Thanks,
Steve
"Max" wrote:
It should work ok, but you may be having problems adapting the
formulas to suit your actual layout over there
Here's a revised set-up which assumes your data and formulae in
"dataprior" are within rows 410 to 1000 (say)
So in sheet: dataprior,
your formulas are within H410:H1000, ie you have in H410:
=IF($A4100,IF(ISERROR(MATCH($B410,dataid,0)),"No Longer Here",""),"")
with H410 copied down to H1000
In a new sheet,
Put in A1:
=IF(dataprior!H410="","",IF(dataprior!H410="No Longer Here",ROW(),""))
Put in B1:
=IF(ROW(A1)COUNT(A:A),"",INDEX(dataprior!$A$410:$ A
$1000,SMALL(A:A,ROW())))
Select A1:B1, copy down to B691. Col B should return the required
results.
Adapt the above to suit your actual ranges.
Let me know here how it went for you
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
On Mar 8, 2:58 am, Steve wrote:
Perfect. Thanks so much. That's exactly what I was looking for. One small
problem though.
The A column references of dataprior! that I wanted bunched up at the top in
the B column of the new sheet are correctly showing the the data
corresponding to row 679 and row 744, but not row 413 and row 645. All 4 of
those rows should qualify for the bunching ( having that no longer here text)
. I checked the dragged formulas all the way down, and they appear to be ok.
Any thoughts ?
Thanks,