Mr Excel & excelisfun Trick 30: Count Bills for Money Transaction

I am trying to make a generic template to get the total from a cell ($1178) in this case, using all the available large bills first.

Qty
3
$100.00 $300.00 3 $100.00 $300.00 $50.00 $0.00 31 $20.00 $620.00 3 $10.00 $30.00 7 $5.00 $35.00 2 $1.00 $2.00 16 $0.25 $4.00 1 $0.10 $0.10 23 $0.05 $1.15 4 $0.01 $0.04 2 $10.00 $20.00 2 $5.00 $10.00 2 $2.00 $4.00 4 $0.50 $2.00 $1,328.29 $150.00 $1,178.29

I need help in extracting the largest bills from avaialble bills to get a set total. I am trying to create atemplate to remove the largest bills from all the avaialble bills.
In the case below, I want to extract exactly $$1,178.29 in largest available bills from a register which has $1328.29 . THis will leave the reigter with $150 exactly in change every time


3 $100.00 $300.00
3 $100.00 $300.00
$50.00 $0.00
31 $20.00 $620.00
3 $10.00 $30.00
7 $5.00 $35.00
2 $1.00 $2.00
16 $0.25 $4.00
1 $0.10 $0.10
23 $0.05 $1.15
4 $0.01 $0.04
2 $10.00 $20.00
2 $5.00 $10.00
2 $2.00 $4.00
4 $0.50 $2.00
$1,328.29



$150.00
$1,178.29

Hi,
I saw EXCELISFUN TRICK 369. I need to do something similar. I tried to follow his code and couldn't. Then I tried copying it and growing his records and still couldn't get it to work.

What I am trying to do is on the first list use an inventory list. Which could be about 2000 - 5000 records or maybe double that (not sure how big of a list I could use in Excel). But lets say it is the max number (if someone could tell me that number I would be most appreciative).

I then will load a second list, or would load a list into second column. I then want the difference (what is missing) from the second list to appear in the third list. If it can give me the row of where it is in the first list that would be great (not a problem if you can't). I just don't know why the code from that video is not working any help would be greatly aprreciated?

Thanks,

Peter Fraga
(fragapete@hotmail.com)

Hi All,

I was setting up a spreadsheet that was based on the following vid:
http://www.youtube.com/user/ExcelIsF...14/tqCEY5YMyqw

Dynamic sub tables based on a master sheet array formula

The formula in question is:
=IF(ROWS(A$7:A7)>$E$1,"",INDEX('2010Corn'!A$4:A$17,SMALL(IF(Table2[From]=$B$1,ROW(Table2[From])-ROW('2010Corn'!$H$4)+1),ROWS(A$7:A7))))

B1 is the customer I'm looking for, E1 is the count for the customer and the master page is 2010Corn. I have 20 sheets looking to this master page for data. It works great, except for an issue when adding a new line in the master table.

What is happening is when I get to the end of a row, I tab to enter a new line in the table. It takes up to a minute for the cell to change color and for me to regain control of the computer.

I have run a performance trace and while the computer is locked, one of the CPU core's is pegged for the whole time with a processor que of up to 10 items at a time.

My question is...does anyone have any hints how to optimize this formula?

Thanks
Tony

I want to manage my cash registers to tell the denominations of large bills to take out from all the available bills in the register and leave exactly $150 back in the drawer.

In the attached file as an example I need to pull (qty/denominations) exactly $1178.29 ($1328.29-$150) in large bills and leave exactly in small bills ($10,5,1, rolls of change and change) back in the cash drawer.
If the cashier needs to make change to achive this, the macro should point that out.
Thanks

Just This Video For Fun

http://www.youtube.com/watch?v=0VvjrldlqI0

Hello dear all,
I am working in a private bank. I am working on a file which will have log of transactions done in a month. I have around 50000 different clients. I want to have answer for the following.
I need to identify 1) how many times a client has done transaction ,,like howman withdrawls, howmany deposits, howmany ATM withdraws etc among all 50 branches and 2) the sum of transactions of that client for each case like withdrawl , deposit etc
I want to use a macro in vb
please help in this
thanks
suryam

Hello,
I apologize in advance, this topic is over my head so I may over explain.

my spread sheet is organized by agent name, then each agent has 4 transactions per row, columns reading:
name, start date, DA# 1, Street 1, Close Date, Bonus $ 1, Posted 1, DA #2, street 2, .....and so in through the 4th DA

Under this table I would like to not only count how many DA's have posted each month, but also total the Bonus paid for that month.
I have columns labeled:

Month, (blank), # of closings, Total Bonus

My problem is I don't know how to tell the formula to count only the dates in January (or any month) when the actual dates are entered in the spreadsheet in the following format 01/01/10, and I need it to search multiple columns (ie, all 4 "posted" columns)

Additional problem... I also have no clue how to total the bonuses for a given month when the $amount is in one column and the date is is paid is in another.

I have searched through HELP and tried COUNTIF and COUNTIFS and tried to use a pivot table. I can't quite seem to get it.

Thank you in advance for any assistance you can provide.

Respectfully,

Hi Guys

I have made this excel for my daily use, But i am facing some problem with this sheet.
Basically i need to monitor bills given out for delivery, and check if they are delivered and returned on a day.( SO that i dont misplace any bills end of the day)

Now the problem i am facing is that when returned bills are sent again for delivery i can't monitor the status, the number just appears only once on the missing bills

I have uploaded my file in this location..please help me

http://rapidshare.com/files/245816357/deli.xls.html

Hi everyone, I'm new here and looking for insight!

I'm currently working with Autodesk Inventor 2010 and assemblies that contains hundreds of pieces. When I need to place an order to our suppliers, I extract a bill of material (bom) in excel format to place the order. Then I continue to make changes to the model and days later, I extract a new BOM.

Now what I want is to automatically compare the 2 BOM and generate a new one that contains only the new parts and the parts that quantity have changed.

See Example below.I don't know what happened but the table is going far down....

(Mods feel free to edit if you can remove that space.)














Original:













Item
QTY
Part Number
REV
TB
Description
Thickness
Material
Color
Stock Number




1.23.2
6
000-0801-04-000-08
2

Window Furring Support
0.06400 in
Galvanized Steel
Na





2.4.4.2
2
000-0801-04-006-05
1

Window Furring Support (Double)
0.06400 in
Galvanized Steel
Na





1.23.1
1
000-0803-01-000-01
1

Window Fur Support
0.06400 in
Galvanized Steel
Na
WF01




2,5
3
000-1010-01-056-01
1

Door Lintel (Alumilex)
0.12300 in
Galvanized Steel
Na
DL5601




47
1
002-1205-01-018-01
2

Post Assembly (Inside Corner)

PCI-018




41
1
002-1205-01-104-01
2

Post Assembly (Inside Corner)

PCI-104-SU




19
1
002-1205-03-120-01
2

Post Assembly (Inside Corner)

PCI-120
































New
one:













Item
QTY
Part Number
REV
TB
Description
Thickness
Material
Color
Stock Number




1.23.2
5
000-0801-04-000-08
2

Window Furring Support
0.06400 in
Galvanized Steel
Na





1.23.1
1
000-0803-01-000-01
1

Window Fur Support
0.06400 in
Galvanized Steel
Na
WF01




2,5
8
000-1010-01-056-01
1

Door Lintel (Alumilex)
0.12300 in
Galvanized Steel
Na
DL5601




1,22
2
000-1110-01-000-01
2

Narrow Window Support
0.12300 in
Galvanized Steel
Na





1,24
1
000-1110-01-000-11
1

Narrow Window Attachment
0.12300 in
Galvanized Steel
Na





19
1
002-1205-03-120-01
2

Post Assembly (Inside Corner)

PCI-120




41
2
002-1205-01-104-01
2

Post Assembly (Inside Corner)

PCI-104-SU








Result:




Item
QTY
Part Number
REV
TB
Description
Thickness
Material
Color
Stock Number




1.23.2
-1
000-0801-04-000-08
2

Window Furring Support
0.06400 in
Galvanized Steel
Na





2.4.4.2
-2
000-0801-04-006-05
1

Window Furring Support (Double)
0.06400 in
Galvanized Steel
Na





2,5
5
000-1010-01-056-01
1

Door Lintel (Alumilex)
0.12300 in
Galvanized Steel
Na
DL5601




1,22
2
000-1110-01-000-01
2

Narrow Window Support
0.12300 in
Galvanized Steel
Na





1,24
1
000-1110-01-000-11
1

Narrow Window Attachment
0.12300 in
Galvanized Steel
Na





47
-1
002-1205-01-018-01
2

Post Assembly (Inside Corner)

PCI-018




41
1
002-1205-01-104-01
2

Post Assembly (Inside Corner)

PCI-104-SU









Each BOM can go down for hundreds of parts and assemblies so this got extremely long and unreliable doing it by hand. If anyone can help I would highly appreciate.

Thanks,
Dox