Help with a Function

rhani111 · #1 07-11-2005, 02:24 AM

I desperately need help with creating a function, macro or anything that you guys can think of to make things easier!!

I have a range of cells that list different information that I need to bring together into another table. This has been easy enough, BUT I need the data to skip zero or blank rows and only list information that is greater than a certain number.

For example
Column A is an ID number
Column B is the location
Column C is the date
Column D is the total tonnes
Column E is the number of Days
Column F is the Actual rate
Column G is the depth

What i want the table to reflect is ONLY the rows that have a figure greater than 25 in the Depth or Column G.

This i have managed quite well, but I don't want rows that are blank or zero in the new table...any ideas????

A macro would be fine here too.....but i would prefer a function????

#2 07-11-2005, 04:05 AM

Let A2:G100 house the data of interest, including the header labels.

A1: 25

which is your Depth citerion value.

H1: 0

which is mandatory.

H2: Idx

which is a label.

H3, copied down:

=IF((A3<>"")*(G2>$A$1),LOOKUP(9.99999999999999E+307,$H$1:H2)+1,"")

J1:

=LOOKUP(9.99999999999999E+307,H1:H00)

J2: Pos

which is juat a label.

J3:

=IF(ROW()-ROW($J$3)+1<=$J$1,MATCH(ROW()-ROW($J$3)+1,$H$3:$H$100),"")

K3, copied across as far as needed then down:

=IF($J3<>"",INDEX(A3:A100,$J3),"")

rhani111 wrote:
> I desperately need help with creating a function, macro or anything that
> you guys can think of to make things easier!!
>
> I have a range of cells that list different information that I need to
> bring together into another table. This has been easy enough, BUT I
> need the data to skip zero or blank rows and only list information that
> is greater than a certain number.
>
> For example
> Column A is an ID number
> Column B is the location
> Column C is the date
> Column D is the total tonnes
> Column E is the number of Days
> Column F is the Actual rate
> Column G is the depth
>
> What i want the table to reflect is ONLY the rows that have a figure
> greater than 25 in the Depth or Column G.
>
> This i have managed quite well, but I don't want rows that are blank or
> zero in the new table...any ideas????
>
> A macro would be fine here too.....but i would prefer a function????
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

rhani111 · #3 07-11-2005, 04:12 AM

ok.....ummm I am more confused than ever with your reply...I have no idea what all that meant at all...lol

can you please explain??

#4 07-11-2005, 07:05 AM

You could use Autofilter.
Assuming you have labels in row 1 to correspond with the information posted,
1. highlight A1:G1
2. Data>Filter>Autofilter
3. Click the dropdown in G1 and select Custom
4. Scroll the left pane to select "is greater than"
5. Enter 25 in the right hand pane

You will now have a table with visible rows only being those with a value of
greater than 25 depth.
NB if you want to include 25 in your sample, then either choose "is greater
than or equal to" or leave it as "is greter than" but set the value to be 24

--
Regards
Roger Govier
"rhani111" <rhani111.1rzu2e_1121065514.8763@excelforum-nospam.com> wrote in
message news:rhani111.1rzu2e_1121065514.8763@excelforum-nospam.com...
>
> I desperately need help with creating a function, macro or anything that
> you guys can think of to make things easier!!
>
> I have a range of cells that list different information that I need to
> bring together into another table. This has been easy enough, BUT I
> need the data to skip zero or blank rows and only list information that
> is greater than a certain number.
>
> For example
> Column A is an ID number
> Column B is the location
> Column C is the date
> Column D is the total tonnes
> Column E is the number of Days
> Column F is the Actual rate
> Column G is the depth
>
> What i want the table to reflect is ONLY the rows that have a figure
> greater than 25 in the Depth or Column G.
>
> This i have managed quite well, but I don't want rows that are blank or
> zero in the new table...any ideas????
>
> A macro would be fine here too.....but i would prefer a function????
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
> http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=386046
>

rhani111 · #5 07-11-2005, 07:13 AM

Hi Roger,

Although that works absolutely brilliantly, I need it to update itself aumatically everytime data is entered into another sheet. This is being created for use by others who are not familiar with Excel functions..

Any ideas??

#6 07-11-2005, 09:05 AM

Hi Rhani

Perhaps using Advanced Filter to extract data from one sheet to another with
the user having entered their criteria on the lookup sheet would be the
answer.

Take a look at the many examples regarding Advanced Filter posted by Debra
Dalgleish on her site
http://www.contextures.com/tiptech.html
and I am sure you will find a solution

--
Regards
Roger Govier
"rhani111" <rhani111.1s07yf_1121083517.2496@excelforum-nospam.com> wrote in
message news:rhani111.1s07yf_1121083517.2496@excelforum-nospam.com...
>
> Hi Roger,
>
> Although that works absolutely brilliantly, I need it to update itself
> aumatically everytime data is entered into another sheet. This is being
> created for use by others who are not familiar with Excel functions..
>
> Any ideas??
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
> http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=386046
>

#7 07-11-2005, 10:05 AM

Doesn't work as intended?

Here an example use of the formula system:

http://www.mrexcel.com/board2/viewtopic.php?t=146895

rhani111 wrote:
> ok.....ummm I am more confused than ever with your reply...I have no
> idea what all that meant at all...lol
>
> can you please explain??
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

#8 09-05-2005, 10:05 PM

Let A2:G100 house the data of interest, including the header labels.

A1: 25

which is your Depth citerion value.

H1: 0

which is mandatory.

H2: Idx

which is a label.

H3, copied down:

=IF((A3<>"")*(G2>$A$1),LOOKUP(9.99999999999999E+307,$H$1:H2)+1,"")

J1:

=LOOKUP(9.99999999999999E+307,H1:H00)

J2: Pos

which is juat a label.

J3:

=IF(ROW()-ROW($J$3)+1<=$J$1,MATCH(ROW()-ROW($J$3)+1,$H$3:$H$100),"")

K3, copied across as far as needed then down:

=IF($J3<>"",INDEX(A3:A100,$J3),"")

rhani111 wrote:
> I desperately need help with creating a function, macro or anything that
> you guys can think of to make things easier!!
>
> I have a range of cells that list different information that I need to
> bring together into another table. This has been easy enough, BUT I
> need the data to skip zero or blank rows and only list information that
> is greater than a certain number.
>
> For example
> Column A is an ID number
> Column B is the location
> Column C is the date
> Column D is the total tonnes
> Column E is the number of Days
> Column F is the Actual rate
> Column G is the depth
>
> What i want the table to reflect is ONLY the rows that have a figure
> greater than 25 in the Depth or Column G.
>
> This i have managed quite well, but I don't want rows that are blank or
> zero in the new table...any ideas????
>
> A macro would be fine here too.....but i would prefer a function????
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

#9 09-05-2005, 10:05 PM

You could use Autofilter.
Assuming you have labels in row 1 to correspond with the information posted,
1. highlight A1:G1
2. Data>Filter>Autofilter
3. Click the dropdown in G1 and select Custom
4. Scroll the left pane to select "is greater than"
5. Enter 25 in the right hand pane

You will now have a table with visible rows only being those with a value of
greater than 25 depth.
NB if you want to include 25 in your sample, then either choose "is greater
than or equal to" or leave it as "is greter than" but set the value to be 24

--
Regards
Roger Govier
"rhani111" <rhani111.1rzu2e_1121065514.8763@excelforum-nospam.com> wrote in
message news:rhani111.1rzu2e_1121065514.8763@excelforum-nospam.com...
>
> I desperately need help with creating a function, macro or anything that
> you guys can think of to make things easier!!
>
> I have a range of cells that list different information that I need to
> bring together into another table. This has been easy enough, BUT I
> need the data to skip zero or blank rows and only list information that
> is greater than a certain number.
>
> For example
> Column A is an ID number
> Column B is the location
> Column C is the date
> Column D is the total tonnes
> Column E is the number of Days
> Column F is the Actual rate
> Column G is the depth
>
> What i want the table to reflect is ONLY the rows that have a figure
> greater than 25 in the Depth or Column G.
>
> This i have managed quite well, but I don't want rows that are blank or
> zero in the new table...any ideas????
>
> A macro would be fine here too.....but i would prefer a function????
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
> http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=386046
>

#10 09-05-2005, 10:05 PM

Hi Rhani

Perhaps using Advanced Filter to extract data from one sheet to another with
the user having entered their criteria on the lookup sheet would be the
answer.

Take a look at the many examples regarding Advanced Filter posted by Debra
Dalgleish on her site
http://www.contextures.com/tiptech.html
and I am sure you will find a solution

--
Regards
Roger Govier
"rhani111" <rhani111.1s07yf_1121083517.2496@excelforum-nospam.com> wrote in
message news:rhani111.1s07yf_1121083517.2496@excelforum-nospam.com...
>
> Hi Roger,
>
> Although that works absolutely brilliantly, I need it to update itself
> aumatically everytime data is entered into another sheet. This is being
> created for use by others who are not familiar with Excel functions..
>
> Any ideas??
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
> http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=386046
>

#11 09-05-2005, 10:05 PM

Doesn't work as intended?

Here an example use of the formula system:

http://www.mrexcel.com/board2/viewtopic.php?t=146895

rhani111 wrote:
> ok.....ummm I am more confused than ever with your reply...I have no
> idea what all that meant at all...lol
>
> can you please explain??
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

#12 09-05-2005, 11:05 PM

Let A2:G100 house the data of interest, including the header labels.

A1: 25

which is your Depth citerion value.

H1: 0

which is mandatory.

H2: Idx

which is a label.

H3, copied down:

=IF((A3<>"")*(G2>$A$1),LOOKUP(9.99999999999999E+307,$H$1:H2)+1,"")

J1:

=LOOKUP(9.99999999999999E+307,H1:H00)

J2: Pos

which is juat a label.

J3:

=IF(ROW()-ROW($J$3)+1<=$J$1,MATCH(ROW()-ROW($J$3)+1,$H$3:$H$100),"")

K3, copied across as far as needed then down:

=IF($J3<>"",INDEX(A3:A100,$J3),"")

rhani111 wrote:
> I desperately need help with creating a function, macro or anything that
> you guys can think of to make things easier!!
>
> I have a range of cells that list different information that I need to
> bring together into another table. This has been easy enough, BUT I
> need the data to skip zero or blank rows and only list information that
> is greater than a certain number.
>
> For example
> Column A is an ID number
> Column B is the location
> Column C is the date
> Column D is the total tonnes
> Column E is the number of Days
> Column F is the Actual rate
> Column G is the depth
>
> What i want the table to reflect is ONLY the rows that have a figure
> greater than 25 in the Depth or Column G.
>
> This i have managed quite well, but I don't want rows that are blank or
> zero in the new table...any ideas????
>
> A macro would be fine here too.....but i would prefer a function????
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

#13 09-05-2005, 11:05 PM

You could use Autofilter.
Assuming you have labels in row 1 to correspond with the information posted,
1. highlight A1:G1
2. Data>Filter>Autofilter
3. Click the dropdown in G1 and select Custom
4. Scroll the left pane to select "is greater than"
5. Enter 25 in the right hand pane

You will now have a table with visible rows only being those with a value of
greater than 25 depth.
NB if you want to include 25 in your sample, then either choose "is greater
than or equal to" or leave it as "is greter than" but set the value to be 24

--
Regards
Roger Govier
"rhani111" <rhani111.1rzu2e_1121065514.8763@excelforum-nospam.com> wrote in
message news:rhani111.1rzu2e_1121065514.8763@excelforum-nospam.com...
>
> I desperately need help with creating a function, macro or anything that
> you guys can think of to make things easier!!
>
> I have a range of cells that list different information that I need to
> bring together into another table. This has been easy enough, BUT I
> need the data to skip zero or blank rows and only list information that
> is greater than a certain number.
>
> For example
> Column A is an ID number
> Column B is the location
> Column C is the date
> Column D is the total tonnes
> Column E is the number of Days
> Column F is the Actual rate
> Column G is the depth
>
> What i want the table to reflect is ONLY the rows that have a figure
> greater than 25 in the Depth or Column G.
>
> This i have managed quite well, but I don't want rows that are blank or
> zero in the new table...any ideas????
>
> A macro would be fine here too.....but i would prefer a function????
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
> http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=386046
>

#14 09-05-2005, 11:05 PM

Hi Rhani

Perhaps using Advanced Filter to extract data from one sheet to another with
the user having entered their criteria on the lookup sheet would be the
answer.

Take a look at the many examples regarding Advanced Filter posted by Debra
Dalgleish on her site
http://www.contextures.com/tiptech.html
and I am sure you will find a solution

--
Regards
Roger Govier
"rhani111" <rhani111.1s07yf_1121083517.2496@excelforum-nospam.com> wrote in
message news:rhani111.1s07yf_1121083517.2496@excelforum-nospam.com...
>
> Hi Roger,
>
> Although that works absolutely brilliantly, I need it to update itself
> aumatically everytime data is entered into another sheet. This is being
> created for use by others who are not familiar with Excel functions..
>
> Any ideas??
>
>
> --
> rhani111
> ------------------------------------------------------------------------
> rhani111's Profile:
> http://www.excelforum.com/member.php...o&userid=19940
> View this thread: http://www.excelforum.com/showthread...hreadid=386046
>

#15 09-05-2005, 11:05 PM

Doesn't work as intended?

Here an example use of the formula system:

http://www.mrexcel.com/board2/viewtopic.php?t=146895

rhani111 wrote:
> ok.....ummm I am more confused than ever with your reply...I have no
> idea what all that meant at all...lol
>
> can you please explain??
>
>

--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.

Welcome to the Excel Forum

Forum Statistics