software

Good Evening

All - I have searched this group high and low for an answer and I can't
seem to grasp anything to create what I'm after.. Though I totally
appreciate all the information

Here it goes...

I have 2 sheets in Excel. The first sheet in Excel lists a master
financial statement description list of the balance sheet, a balance
sheet if you will

First Sheet - Master Balance Sheet

cash
accounts receivable
prepaid assets

I assigned a numerical value for each
Cash = 1
Accounts Receivable = 2
prepaid assets = 3
All the way from quot;1quot; = Cash to quot;41quot; = Total Liabilites and Owners
Equity

Second Sheet - Balance Sheet has different descriptions but in the end
only 41 different numbers will be picked up from sheet 2

This sheet contains a financial statement with

Cash = quot;1quot; 40,000
Money Market = quot;1quot; 20,000
Accounts Receivable = quot;2quot; 20,000
Prepaid Assets = quot;3quot; 10,000

All the way down to quot;41quot; = Total Liabilites and Owners Equity

I KNOW THAT VLOOKUP WILL ONLY PICK UP THE 40,000 and not the 20,000
because VLOOKUP will only pick up one instance...

How do I get Excel to pick up all instances of 1 all instances of 2
etc... without using SUMIF?
I tried sumif, but that didn't seem to work right ...
Thanks,
ExcelUser777Hi!

So, what do you want to do, list all amounts that coresspond to a certain
number category?

Lookup all instances of 1 and return:

40,000
20,000

Biff

quot;ExcelUser777quot; gt; wrote in message oups.com...
gt; Good Evening
gt;
gt; All - I have searched this group high and low for an answer and I can't
gt; seem to grasp anything to create what I'm after.. Though I totally
gt; appreciate all the information
gt;
gt; Here it goes...
gt;
gt; I have 2 sheets in Excel. The first sheet in Excel lists a master
gt; financial statement description list of the balance sheet, a balance
gt; sheet if you will
gt;
gt; First Sheet - Master Balance Sheet
gt;
gt; cash
gt; accounts receivable
gt; prepaid assets
gt;
gt; I assigned a numerical value for each
gt; Cash = 1
gt; Accounts Receivable = 2
gt; prepaid assets = 3
gt; All the way from quot;1quot; = Cash to quot;41quot; = Total Liabilites and Owners
gt; Equity
gt;
gt; Second Sheet - Balance Sheet has different descriptions but in the end
gt; only 41 different numbers will be picked up from sheet 2
gt;
gt; This sheet contains a financial statement with
gt;
gt; Cash = quot;1quot; 40,000
gt; Money Market = quot;1quot; 20,000
gt; Accounts Receivable = quot;2quot; 20,000
gt; Prepaid Assets = quot;3quot; 10,000
gt;
gt; All the way down to quot;41quot; = Total Liabilites and Owners Equity
gt;
gt; I KNOW THAT VLOOKUP WILL ONLY PICK UP THE 40,000 and not the 20,000
gt; because VLOOKUP will only pick up one instance...
gt;
gt; How do I get Excel to pick up all instances of 1 all instances of 2
gt; etc... without using SUMIF?
gt; I tried sumif, but that didn't seem to work right ...
gt;
gt;
gt;
gt; Thanks,
gt; ExcelUser777
gt;
Wow what a quick response
Hi Biff

Yes I would like to be able to do what you mentioned.
On the second sheet I might have instances of 3 threes etc. 4 fours
etc. etc...
Vlookup is limited for this type of issue?

My goal is to set up a Macro to handle the description grouping.. but I
am creating this manual first and then automate later..I actually just got the sumif to work for me, but I read that Sumif can
be faulty when you are linking files and I use alot of linked files.
I noticed alot of posting using arrays, but these solutions seemed to
be if people wanted to multiply figures in one column 1 by column 2Thanks,
ExcelUser777Ok, I'm still not 100% sure of what you want to do but have a look at this
sample file:

s63.yousendit.com/d.aspx?id=1...00K1V3DOANEYN6

Is that what you had in mind?

The formula that returns the category amounts is an array formula. It must
be copied to enough cells so that all the coressponding amounts are
returned. Since the number of amounts vary by category you need to copy the
formula to enough cells that will cover the maximum number of amounts for
ANY category.

Biff

quot;ExcelUser777quot; gt; wrote in message oups.com...
gt; Wow what a quick response
gt; Hi Biff
gt;
gt; Yes I would like to be able to do what you mentioned.
gt; On the second sheet I might have instances of 3 threes etc. 4 fours
gt; etc. etc...
gt; Vlookup is limited for this type of issue?
gt;
gt; My goal is to set up a Macro to handle the description grouping.. but I
gt; am creating this manual first and then automate later..
gt;
gt;
gt; I actually just got the sumif to work for me, but I read that Sumif can
gt; be faulty when you are linking files and I use alot of linked files.
gt; I noticed alot of posting using arrays, but these solutions seemed to
gt; be if people wanted to multiply figures in one column 1 by column 2
gt;
gt;
gt; Thanks,
gt; ExcelUser777
gt;
That's great.

“Biff”编写：

gt; Hi!
gt;
gt; So, what do you want to do, list all amounts that coresspond to a certain
gt; number category?
gt;
gt; Lookup all instances of 1 and return:
gt;
gt; 40,000
gt; 20,000
gt;
gt; Biff
gt;
gt; quot;ExcelUser777quot; gt; wrote in message
gt; oups.com...
gt; gt; Good Evening
gt; gt;
gt; gt; All - I have searched this group high and low for an answer and I can't
gt; gt; seem to grasp anything to create what I'm after.. Though I totally
gt; gt; appreciate all the information
gt; gt;
gt; gt; Here it goes...
gt; gt;
gt; gt; I have 2 sheets in Excel. The first sheet in Excel lists a master
gt; gt; financial statement description list of the balance sheet, a balance
gt; gt; sheet if you will
gt; gt;
gt; gt; First Sheet - Master Balance Sheet
gt; gt;
gt; gt; cash
gt; gt; accounts receivable
gt; gt; prepaid assets
gt; gt;
gt; gt; I assigned a numerical value for each
gt; gt; Cash = 1
gt; gt; Accounts Receivable = 2
gt; gt; prepaid assets = 3
gt; gt; All the way from quot;1quot; = Cash to quot;41quot; = Total Liabilites and Owners
gt; gt; Equity
gt; gt;
gt; gt; Second Sheet - Balance Sheet has different descriptions but in the end
gt; gt; only 41 different numbers will be picked up from sheet 2
gt; gt;
gt; gt; This sheet contains a financial statement with
gt; gt;
gt; gt; Cash = quot;1quot; 40,000
gt; gt; Money Market = quot;1quot; 20,000
gt; gt; Accounts Receivable = quot;2quot; 20,000
gt; gt; Prepaid Assets = quot;3quot; 10,000
gt; gt;
gt; gt; All the way down to quot;41quot; = Total Liabilites and Owners Equity
gt; gt;
gt; gt; I KNOW THAT VLOOKUP WILL ONLY PICK UP THE 40,000 and not the 20,000
gt; gt; because VLOOKUP will only pick up one instance...
gt; gt;
gt; gt; How do I get Excel to pick up all instances of 1 all instances of 2
gt; gt; etc... without using SUMIF?
gt; gt; I tried sumif, but that didn't seem to work right ...
gt; gt;
gt; gt;
gt; gt;
gt; gt; Thanks,
gt; gt; ExcelUser777
gt; gt;
gt;
gt;
gt;

Ooops!

Caught a bug!

Change this portion of the array formula:

=IF(ROWS($1:1)lt;=B$2

To:

=IF(AND(ROWS($1:1)lt;=B$2,B$2lt;gt;quot;quot;)

This modification will account for cell B2 being empty. (which currently
causes an error)

Biff

quot;Biffquot; gt; wrote in message
...
gt; Ok, I'm still not 100% sure of what you want to do but have a look at this
gt; sample file:
gt;
gt; s63.yousendit.com/d.aspx?id=1...00K1V3DOANEYN6
gt;
gt; Is that what you had in mind?
gt;
gt; The formula that returns the category amounts is an array formula. It must
gt; be copied to enough cells so that all the coressponding amounts are
gt; returned. Since the number of amounts vary by category you need to copy
gt; the formula to enough cells that will cover the maximum number of amounts
gt; for ANY category.
gt;
gt; Biff
gt;
gt; quot;ExcelUser777quot; gt; wrote in message
gt; oups.com...
gt;gt; Wow what a quick response
gt;gt; Hi Biff
gt;gt;
gt;gt; Yes I would like to be able to do what you mentioned.
gt;gt; On the second sheet I might have instances of 3 threes etc. 4 fours
gt;gt; etc. etc...
gt;gt; Vlookup is limited for this type of issue?
gt;gt;
gt;gt; My goal is to set up a Macro to handle the description grouping.. but I
gt;gt; am creating this manual first and then automate later..
gt;gt;
gt;gt;
gt;gt; I actually just got the sumif to work for me, but I read that Sumif can
gt;gt; be faulty when you are linking files and I use alot of linked files.
gt;gt; I noticed alot of posting using arrays, but these solutions seemed to
gt;gt; be if people wanted to multiply figures in one column 1 by column 2
gt;gt;
gt;gt;
gt;gt; Thanks,
gt;gt; ExcelUser777
gt;gt;
gt;
gt;
Biff,

This is excellent...when I finally get it working

The Array doesn't seem to work when you choose 5 from the drop down
list.Thanks,
ExcelUser777Biff,

Thanks a bunch
I love how I can choose the number from your Excel Spreadsheet.

What I want to have happen is to show all instances of quot;1quot; summed up
into one cell, all instances of quot;2quot; summed up into one cell, what you
did is great

Sumif works...I just don't know if that is the best way do it....gt; The Array doesn't seem to work when you choose 5 from the drop down
gt; list.

It works for me.

Biff

quot;ExcelUser777quot; gt; wrote in message ups.com...
gt; Biff,
gt;
gt; This is excellent...when I finally get it working
gt;
gt; The Array doesn't seem to work when you choose 5 from the drop down
gt; list.
gt;
gt;
gt; Thanks,
gt; ExcelUser777
gt;
You're welcome. Thanks for the feedback!

Biff

quot;ExcelUser777quot; gt; wrote in message ups.com...
gt; Biff,
gt;
gt; Thanks a bunch
gt; I love how I can choose the number from your Excel Spreadsheet.
gt;
gt; What I want to have happen is to show all instances of quot;1quot; summed up
gt; into one cell, all instances of quot;2quot; summed up into one cell, what you
gt; did is great
gt;
gt; Sumif works...I just don't know if that is the best way do it....
gt;