Formula function(s) to do this? Or is it a formula at all?
 

I'm the first to admit I'm not the best Excel user, so I'm coming to you guys
for help. Here's what I have:

Sheet 1, Column E contains a G/L number, like this: 12345-1001 or
12345-0057; the cells are formatted as text. Column G contains the
Description of that G/L number (text) and Column M contains a cost (formatted
as Accounting w/ 2 decimal places).

Here's what I've been asked to do:

Create a formula so that the user can (on Sheet 2), type in cell A1 the G/L
prefix, which is the first 5 digits of the G/L number (e.g., 12345) and the
formula finds all the G/L numbers on Sheet 1 that begin with those 5
characters and copies the information from Column E (the G/L number), Column
G (the description) and Column M (the cost).

Then, below that, the user will type in the next G/L prefix (ie, 22345) and
the formula would find the matches and paste the data, etc.

I hope I'm explaining this clearly. I've read so many articles this morning
trying to figure out what this formula looks like that I think I'm more
confused now than when I started. I'm using Excel 2003.

ANY help would be GREATLY appreciated!

Formula function(s) to do this? Or is it a formula at all?
 

Yes, that can be done. Can you tell me first, though, how many columns
you use in your Sheet1 (i.e. what is the next free column), do you
have a header row so that the data starts on row 2, and do you have
any objections to putting some formulae in Sheet1 which will enable
the formulae in the new Sheet2 to work? Also, what is the maximum
number of entries you are likely to expect for any G/L prefix?

Pete

On Jul 23, 3:06*pm, Julia wrote:
I'm the first to admit I'm not the best Excel user, so I'm coming to you guys
for help. Here's what I have:

Sheet 1, Column E contains a G/L number, like this: 12345-1001 or
12345-0057; the cells are formatted as text. Column G contains the
Description of that G/L number (text) and Column M contains a cost (formatted
as Accounting w/ 2 decimal places).

Here's what I've been asked to do:

Create a formula so that the user can (on Sheet 2), type in cell A1 the G/L
prefix, which is the first 5 digits of the G/L number (e.g., 12345) and the
formula finds all the G/L numbers on Sheet 1 that begin with those 5
characters and copies the information from Column E (the G/L number), Column
G (the description) and Column M (the cost).

Then, below that, the user will type in the next G/L prefix (ie, 22345) and
the formula would find the matches and paste the data, etc.

I hope I'm explaining this clearly. I've read so many articles this morning
trying to figure out what this formula looks like that I think I'm more
confused now than when I started. I'm using Excel 2003.

ANY help would be GREATLY appreciated!

Formula function(s) to do this? Or is it a formula at all?
 

Thanks for your response!

The next free column on Sheet 1 was Q...I say "was" because I just added a
formula there to display the 5-digit G/L prefix for that line item -- so it's
extracting the first five digits from the full G/L number in Column E. I used
this formula: =LEFT($E21,FIND("-",$E21)-1).

The maximum number of G/L line items for any prefix would be 15.

Many thanks!


"Pete_UK" wrote:

Yes, that can be done. Can you tell me first, though, how many columns
you use in your Sheet1 (i.e. what is the next free column), do you
have a header row so that the data starts on row 2, and do you have
any objections to putting some formulae in Sheet1 which will enable
the formulae in the new Sheet2 to work? Also, what is the maximum
number of entries you are likely to expect for any G/L prefix?

Pete

On Jul 23, 3:06 pm, Julia wrote:
I'm the first to admit I'm not the best Excel user, so I'm coming to you guys
for help. Here's what I have:

Sheet 1, Column E contains a G/L number, like this: 12345-1001 or
12345-0057; the cells are formatted as text. Column G contains the
Description of that G/L number (text) and Column M contains a cost (formatted
as Accounting w/ 2 decimal places).

Here's what I've been asked to do:

Create a formula so that the user can (on Sheet 2), type in cell A1 the G/L
prefix, which is the first 5 digits of the G/L number (e.g., 12345) and the
formula finds all the G/L numbers on Sheet 1 that begin with those 5
characters and copies the information from Column E (the G/L number), Column
G (the description) and Column M (the cost).

Then, below that, the user will type in the next G/L prefix (ie, 22345) and
the formula would find the matches and paste the data, etc.

I hope I'm explaining this clearly. I've read so many articles this morning
trying to figure out what this formula looks like that I think I'm more
confused now than when I started. I'm using Excel 2003.

ANY help would be GREATLY appreciated!

Formula function(s) to do this? Or is it a formula at all?
 

First, on Sheet2 format column A as Text.

Then in B1, enter this formula

=MAX(IF($A$1:$A1<"",ROW($A$1:$A1)))

In C1, enter

=IF(ISERROR(SMALL(IF(LEFT(Sheet1!$E$1:$E$20,5)=IND EX($A:$A,$B1),ROW($A$1:$A$20),""),ROW($A1)-$B1+1)),"",
INDEX(Sheet1!$G$1:$G$20,SMALL(IF(LEFT(Sheet1!$E$1: $E$20,5)=INDEX($A:$A,$B1),ROW($A$1:$A$20),""),ROW( $A1)-$B1+1)))

In D1, enter

=IF(ISERROR(SMALL(IF(LEFT(Sheet1!$E$1:$E$20,5)=IND EX($A:$A,$B1),ROW($A$1:$A$20),""),ROW($A1)-$B1+1)),"",
INDEX(Sheet1!$M$1:$M$20,SMALL(IF(LEFT(Sheet1!$E$1: $E$20,5)=INDEX($A:$A,$B1),ROW($A$1:$A$20),""),ROW( $A1)-$B1+1)))

These are all array formulae, they should be committed with
Ctrl-Shift-Enter, not just Enter.
Excel will automatically enclose the formula in braces (curly brackets), do
not try to do this manually.
When editing the formula, it must again be array-entered.

Then just copy B1:D1 down, as soon as it runs out of itmes it returns
blanks, so you can add another GL code from thereon.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Julia" wrote in message
...
I'm the first to admit I'm not the best Excel user, so I'm coming to you
guys
for help. Here's what I have:

Sheet 1, Column E contains a G/L number, like this: 12345-1001 or
12345-0057; the cells are formatted as text. Column G contains the
Description of that G/L number (text) and Column M contains a cost
(formatted
as Accounting w/ 2 decimal places).

Here's what I've been asked to do:

Create a formula so that the user can (on Sheet 2), type in cell A1 the
G/L
prefix, which is the first 5 digits of the G/L number (e.g., 12345) and
the
formula finds all the G/L numbers on Sheet 1 that begin with those 5
characters and copies the information from Column E (the G/L number),
Column
G (the description) and Column M (the cost).

Then, below that, the user will type in the next G/L prefix (ie, 22345)
and
the formula would find the matches and paste the data, etc.

I hope I'm explaining this clearly. I've read so many articles this
morning
trying to figure out what this formula looks like that I think I'm more
confused now than when I started. I'm using Excel 2003.

ANY help would be GREATLY appreciated!

Formula function(s) to do this? Or is it a formula at all?
 

I see Bob's given you a solution, so I'll hold on to see if that works
for you.

Pete

On Jul 23, 3:36*pm, Julia wrote:
Thanks for your response!

The next free column on Sheet 1 was Q...I say "was" because I just added a
formula there to display the 5-digit G/L prefix for that line item -- so it's
extracting the first five digits from the full G/L number in Column E. I used
this formula: =LEFT($E21,FIND("-",$E21)-1).

The maximum number of G/L line items for any prefix would be 15.

Many thanks!



"Pete_UK" wrote:
Yes, that can be done. Can you tell me first, though, how many columns
you use in your Sheet1 (i.e. what is the next free column), do you
have a header row so that the data starts on row 2, and do you have
any objections to putting some formulae in Sheet1 which will enable
the formulae in the new Sheet2 to work? Also, what is the maximum
number of entries you are likely to expect for any G/L prefix?

Pete

On Jul 23, 3:06 pm, Julia wrote:
I'm the first to admit I'm not the best Excel user, so I'm coming to you guys
for help. Here's what I have:

Sheet 1, Column E contains a G/L number, like this: 12345-1001 or
12345-0057; the cells are formatted as text. Column G contains the
Description of that G/L number (text) and Column M contains a cost (formatted
as Accounting w/ 2 decimal places).

Here's what I've been asked to do:

Create a formula so that the user can (on Sheet 2), type in cell A1 the G/L
prefix, which is the first 5 digits of the G/L number (e.g., 12345) and the
formula finds all the G/L numbers on Sheet 1 that begin with those 5
characters and copies the information from Column E (the G/L number), Column
G (the description) and Column M (the cost).

Then, below that, the user will type in the next G/L prefix (ie, 22345) and
the formula would find the matches and paste the data, etc.

I hope I'm explaining this clearly. I've read so many articles this morning
trying to figure out what this formula looks like that I think I'm more
confused now than when I started. I'm using Excel 2003.

ANY help would be GREATLY appreciated!- Hide quoted text -

- Show quoted text -