Creating a list from set criteria

[donh]

Hi

I have 12 tables each consisting of 2 rows and 31 columns max. Each
table details the date in the top row and number of staff at work in
the bottom row. So all 12 cover number of staff at work for year.

I need to identify the dates where staff numbers fall below 4 and
compile this into a list of dates where this occured. 12 seperate
lists would be fine.

I've looked through my formulas that this group has helped me with and
modified one that identifies the column number where workers fall
below 4 but cant get a formula to work that would lookup the
corresponding row and transpose it into a listed table.

=IF(ISNUMBER(MATCH(E19,{0;1;2;3},0)),COLUMN(),"")

As always any help would be appreciated

DonH

 

[Max]

One way ..

Assume a typical table's data within Sheet1's A1:AE2
where A1:AE1 = dates, A2:AE2 = values (staffing #)

In another sheet,

Put in A1:
=IF(INDEX(Sheet1!$2:$2,ROW())<4,ROW(),"")

Put in B1:
=IF(ROW()>COUNT(A:A),"",INDEX(Sheet1!$1:$1,SMALL(A:A,ROW())))
Format B1 as date

Select A1:B1, copy down to B31. Hide away col A. Col B will return the
required results from Sheet1, ie the dates where the staffing # is below 4,
with all results neatly bunched at the top. Note that blank cells, if any,
within Sheet1's A2:AE2 will be treated as zeros.

 

[donh]

One way ..

Assume a typical table's data within Sheet1's A1:AE2
where A1:AE1 = dates, A2:AE2 = values (staffing #)

In another sheet,

Put in A1:
=IF(INDEX(Sheet1!$2:$2,ROW())<4,ROW(),"")

Put in B1:
=IF(ROW()>COUNT(A:A),"",INDEX(Sheet1!$1:$1,SMALL(A:A,ROW())))
Format B1 as date

Select A1:B1, copy down to B31. Hide away col A. Col B will return the
required results from Sheet1, ie the dates where the staffing # is below 4,
with all results neatly bunched at the top. Note that blank cells, if any,
within Sheet1's A2:AE2 will be treated as zeros.
--
Max
Singaporehttp://savefile.com/projects/236895

- Show quoted text -

Many thanks Max, works fine!

Two things though, I tried on my original to refrence <4 to a cell as
this is a variable based on the location of work . I've tried "<H5"
as you would for a countif with your suggestion without success. Can
this be done?

Secondly I'm trying to understand how the formula works so I can
attempt it myself in future but I dont understand how
SMALL(A:A,ROW()))) works, could you give me some background on this
please.

Many thanks again,



DonH

 

[Max]

Many thanks Max, works fine!

Good to hear that. You're welcome.

Two things though, I tried on my original to reference <4 to a cell as
this is a variable based on the location of work . I've tried "<H5"
as you would for a countif with your suggestion without success. Can
this be done?

If you have in H5: 4
just amend the formula in A1 like this:
=IF(INDEX(Sheet1!$2:$2,ROW())<$H$5,ROW(),"")
and copy it down to A31

Secondly I'm trying to understand how the formula works so I can
attempt it myself in future but I dont understand how
SMALL(A:A,ROW()))) works, could you give me some background on this
please.

ROW() is used as the incrementer here. It returns the row number of
the cell it is in. Eg: in any top row cell, say in B1: =ROW() will
return 1. When B1 is copied down, ROW() will simply return 2,3,4... in
B2, B3, B4 ... Try this to see for yourself.

Hence in any top row cell, eg in B1: =SMALL(A:A,ROW())
is the same as : =SMALL(A:A,1) which returns the smallest number in
col A

And when B1 is copied down, the formula resolves to:
In B2: =SMALL(A:A,2) - returns the 2nd smallest number in col A
In B3: =SMALL(A:A,3) - returns the 3rd smallest number in col A
and so on ..


---

 

[donh]

Good to hear that. You're welcome.


If you have in H5: 4
just amend the formula in A1 like this:
=IF(INDEX(Sheet1!$2:$2,ROW())<$H$5,ROW(),"")
and copy it down to A31


ROW() is used as the incrementer here. It returns the row number of
the cell it is in. Eg: in any top row cell, say in B1: =ROW() will
return 1. When B1 is copied down, ROW() will simply return 2,3,4... in
B2, B3, B4 ... Try this to see for yourself.

Hence in any top row cell, eg in B1: =SMALL(A:A,ROW())
is the same as : =SMALL(A:A,1) which returns the smallest number in
col A

And when B1 is copied down, the formula resolves to:
In B2: =SMALL(A:A,2) - returns the 2nd smallest number in col A
In B3: =SMALL(A:A,3) - returns the 3rd smallest number in col A
and so on ..

--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

Max,

Thanks for taking time to reply! I think I understand now!

Two further questions (one of which could deserve a different heading)


Can you have =SMALL(A:A,ROW()) start part way down a column to allow
for headers, or is this better left to a separate display table.

Which links me on to my next question. So far I have an overview of
each location on a separate sheet with your formula being duplicated
12 times to give me a view of the whole year, but I would also like to
bring this together in an a grand overview which sees all twelve
months data from the columns you have helped me with stacked on top of
one another (without gaps) which I can then combine (in different
columns) with all the other locations. Headers allowed would be good.

Sorry if this should of been posted separately.



Thanks for help

DonH

 

[Max]

Max,
Thanks for taking time to reply! I think I understand now!

Glad to hear that ..

Two further questions (one of which could deserve a different heading)

Can you have =SMALL(A:A,ROW())
start part way down a column to allow
for headers, or is this better left to a separate display table.

You can, but you'd need to adjust for this.

If you want to start the extracts from row2 down instead of the top row
Put in A2:
=IF(INDEX(Sheet1!$2:$2,ROW()-1)<$H$5,ROW(),"")
(Leave A1 blank)

Put in B2:
=IF(ROW()-1>COUNT(A:A),"",INDEX(Sheet1!$1:$1,SMALL(A:A,ROW()-1)-1))
Select A2:B2, then copy down by 31 rows to B32.
(B1 can contain a header)

The "-1" parts above are the required arithmetic adjustments.

Which links me on to my next question. So far I have an overview of
each location on a separate sheet with your formula being duplicated
12 times to give me a view of the whole year, but I would also like to
bring this together in an a grand overview which sees all twelve
months data from the columns you have helped me with stacked on top of
one another (without gaps) which I can then combine (in different
columns) with all the other locations. Headers allowed would be good.

One way to achieve this ..

First, in an empty col, just use simple link formulas to stack sequentially
the outputs from each of the twelve "31 cell" column ranges. Example, if you
have month1's outputs in B2:B32, month2's outputs in D232, etc, then using
say, col AA, put in AA2: =B2, copy down to AA32, then put in AA33: =D2, copy
down to AA63, and so on (a one-time job).

Then place in AB2: =IF(OR(AA2={"",0}),"",ROW())
(Leave AB1 blank)

Place in AC2:
=IF(ROW()-1>COUNT(AB:AB),"",INDEX(AA:AA,SMALL(AB:AB,ROW()-1)))

Select AB2:AC2, fill down to the last row in col AA. Col AC will return the
required stacked results (w/o gaps) for your grand overview

---

 

[donh]

Glad to hear that ..




You can, but you'd need to adjust for this.

If you want to start the extracts from row2 down instead of the top row
Put in A2:
=IF(INDEX(Sheet1!$2:$2,ROW()-1)<$H$5,ROW(),"")
(Leave A1 blank)

Put in B2:
=IF(ROW()-1>COUNT(A:A),"",INDEX(Sheet1!$1:$1,SMALL(A:A,ROW()-1)-1))
Select A2:B2, then copy down by 31 rows to B32.
(B1 can contain a header)

The "-1" parts above are the required arithmetic adjustments.


One way to achieve this ..

First, in an empty col, just use simple link formulas to stack sequentially
the outputs from each of the twelve "31 cell" column ranges. Example, if you
have month1's outputs in B2:B32, month2's outputs in D232, etc, then using
say, col AA, put in AA2: =B2, copy down to AA32, then put in AA33: =D2, copy
down to AA63, and so on (a one-time job).

Then place in AB2: =IF(OR(AA2={"",0}),"",ROW())
(Leave AB1 blank)

Place in AC2:
=IF(ROW()-1>COUNT(AB:AB),"",INDEX(AA:AA,SMALL(AB:AB,ROW()-1)))

Select AB2:AC2, fill down to the last row in col AA. Col AC will return the
required stacked results (w/o gaps) for your grand overview
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
---

Cool I'll go away now

Many thanks

 

[Max]

Cool I'll go away now
Many thanks

You're welcome.
Glad you got it going there <g>

---