If specific text result,... corresponing cell info in another colu

S

Steve

I have the below on 2 separate data sheets, indicating if an employee is no
longer here or if a new employee. These are in column H of the data sheets.

=IF($A413>0,IF(ISERROR(MATCH($B413,dataID,0)),"No Longer Here",""),"")

=IF(A514>0,IF(ISERROR(MATCH($B514,PriorDataID,0)),"New Employee",""),"")

If a no longer here result is displayed in dataprior!H413, can I get the
cell value of A413 to display in another sheet, such as in cell B1 for the
first incident , the next in B2, ? The no longer here, and new employee
results are very random, and will be in various rows.

Thanks,

Steve
 
M

Max

One play which gets you there, Steve ..

Assume in sheet: dataprior,
you have your formula in H2 down:
=IF($A2>0,IF(ISERROR(MATCH($B2,dataID,0)),"No Longer Here",""),"")

Then in a new sheet,
Put in A1:
=IF(dataprior!H2="","",IF(dataprior!H2="No Longer Here",ROW(),""))

[Here, we could also have used the simpler: =IF(dataprior!H2="","",ROW()) in
A1 since the only indications evaluated in col H in dataprior would be either
"" or "No Longer Here".]

Put in B1:
=IF(ROW()>COUNT(A:A),"",INDEX(dataprior!A:A,SMALL(A:A,ROW())+1))
Select A1:B1, copy down to cover the max expected extent of col H in
dataprior. Hide away col A. You'd get the results you seek in col B, all
neatly bunched at the top. Do likewise to extract it for "New Employee".
 
S

Steve

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,


Max said:
One play which gets you there, Steve ..

Assume in sheet: dataprior,
you have your formula in H2 down:
=IF($A2>0,IF(ISERROR(MATCH($B2,dataID,0)),"No Longer Here",""),"")

Then in a new sheet,
Put in A1:
=IF(dataprior!H2="","",IF(dataprior!H2="No Longer Here",ROW(),""))

[Here, we could also have used the simpler: =IF(dataprior!H2="","",ROW()) in
A1 since the only indications evaluated in col H in dataprior would be either
"" or "No Longer Here".]

Put in B1:
=IF(ROW()>COUNT(A:A),"",INDEX(dataprior!A:A,SMALL(A:A,ROW())+1))
Select A1:B1, copy down to cover the max expected extent of col H in
dataprior. Hide away col A. You'd get the results you seek in col B, all
neatly bunched at the top. Do likewise to extract it for "New Employee".

Steve said:
I have the below on 2 separate data sheets, indicating if an employee is no
longer here or if a new employee. These are in column H of the data sheets.

=IF($A413>0,IF(ISERROR(MATCH($B413,dataID,0)),"No Longer Here",""),"")

=IF(A514>0,IF(ISERROR(MATCH($B514,PriorDataID,0)),"New Employee",""),"")

If a no longer here result is displayed in dataprior!H413, can I get the
cell value of A413 to display in another sheet, such as in cell B1 for the
first incident , the next in B2, ? The no longer here, and new employee
results are very random, and will be in various rows.

Thanks,

Steve
Click to expand...
Click to expand...
 
M

Max

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($A410>0,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
 
S

Steve

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
 
M

Max

Steve,

Think we can revert to using the simpler entire col refs now that you've
exposed your layout

In your checksheet,

Put in A3:
=IF(dataprior!H3="","",IF(dataprior!H3="No Longer Here",ROW(),""))
(Ensure that A1:A2 are left blank)

Put in B3:
=IF(ROW(A1)>COUNT(A:A),"",INDEX(dataprior!A:A,SMALL(A:A,ROW(A1))))
Select A3:B3, fill down as far as required to cover the extent of source
data. It should work fine now.
 
Top