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

[Julia]

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!

 

[Pete_UK]

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

 

[Julia]

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!

 

[Bob Phillips]

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)=INDEX($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)=INDEX($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 B11 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)

 

[Pete_UK]

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

Pete