Finding values

  • Thread starter Thread starter Chris Lane
  • Start date Start date
C

Chris Lane

I have a spreadsheet that I use the Subtotal function to total values in
Column G based on items in Column A. Column A contain branch names, and
column G contains invoice totals for invoice numbers in column B.

For example, after performing SUBTOTAL, I get the following results:
Column A Column G
Row 329 CTN Total 100 000
Row 562 DBN Total 50 000
Row 649 JHB Total 75 000
Row 683 DFM Total 95 000

What I need is to find CTN Total name in column A, then copy the invoice
total (100 000) in column G into another cell (say T1). T2 would then a
variation of the same formula to show the invoice total of DBN Total, and so
on. I have been trying the MATCH, SUMIF, FIND, IF and INDEX functions, but
cannot get it right. Obviously the row numbers of the branches changes
regularly as invoices are produced.

Can anyone help, please?
 
Try this ..

List the branch names in say, S1:S4, i.e.:

CTN
DBN
JHB
DFM

Then put in T1, copy down to T4:
=IF(ISNA(MATCH(S1&" Total",A:A,0)),"",
INDEX(G:G,MATCH(S1&" Total",A:A,0)))

T1:T4 should return the desired results from col G
 
Chris,

Here is a solution

First, determine how many totals that you want to cater for and then select
that many cells down from T1. Then, enter this formula into the formula bar

=IF(ISERROR(SMALL(IF(ISNUMBER(FIND("Total",$A$1:$A$20)),ROW($A$1:$A$20),""),
ROW($A$1:$A$20))),"",
INDEX(A$1:A$20,SMALL(IF(ISNUMBER(FIND("Total",$A$1:$A$20)),ROW($A$1:$A$20),"
"),ROW($A$1:$A$20))))

as an array, commit with Ctrl-Shift-Enter.

Then just copy and paste that T column block to the same size block in U.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Thanks Max, works perfectly




Max said:
Try this ..

List the branch names in say, S1:S4, i.e.:

CTN
DBN
JHB
DFM

Then put in T1, copy down to T4:
=IF(ISNA(MATCH(S1&" Total",A:A,0)),"",
INDEX(G:G,MATCH(S1&" Total",A:A,0)))

T1:T4 should return the desired results from col G
Click to expand...
 
Thanks very much, Bob


Bob Phillips said:
Chris,

Here is a solution

First, determine how many totals that you want to cater for and then
select
that many cells down from T1. Then, enter this formula into the formula
bar

=IF(ISERROR(SMALL(IF(ISNUMBER(FIND("Total",$A$1:$A$20)),ROW($A$1:$A$20),""),
ROW($A$1:$A$20))),"",
INDEX(A$1:A$20,SMALL(IF(ISNUMBER(FIND("Total",$A$1:$A$20)),ROW($A$1:$A$20),"
"),ROW($A$1:$A$20))))

as an array, commit with Ctrl-Shift-Enter.

Then just copy and paste that T column block to the same size block in U.

--

HTH

RP
(remove nothere from the email address if mailing direct)
Click to expand...
 
Back
Top