Totals And Listing If Not 0

Nov 22, 2012

I'm tracking 50 food items (in column A) for 40 homes (columns B-AO) where each home gets none or varying quantities for each food item (ex: apples 7 for home B, 0 for homes C-L, 19 for home M, etc.). I can get grand totals for each item for all homes, but need to also list how many for each home receiving apples. Ex: 4 homes get apples (4, 7, 19, 1 respectively). SO I want my total sheet to have a column for each food item with the grand total "31" and the second column to show 4 + 7 + 19 + 1. The quantities change frequently so I need a formula I can use for each food item. Also I don't want "zeroes" included. What formula would I use to do this?

View 3 Replies


ADVERTISEMENT

Listing Required

Jun 17, 2009

I have been given a worksheet which has 5000 rows spread along 13 columns.
There are about 200 cells that I am interested in from this sheet, these cells being the subtotals of the cells above them.

I have extracted the individual cells containing the subtotal values to a new, empty column but they are spread down this column with blank cells in between.

Is there a function / formula to get rid of the 4800 blank cells so that the data I need is compressed into a stack of 200 cells all filled with data?

View 14 Replies View Related

Listing Folders With VBA

Oct 10, 2008

I have found a lot of posts which show me how to list a directory tree in an Excel file, usually using a macro. I'm just uninitiated with VBA / VBS and simply don't have the time to figure it out now (it really isn't part of my job description... I'm rather doing a favor here).

I need to be able to re-generate an excel-based list of clients at will. The client names are subdirectories of different case types. There aren't tons of case types, right now maybe 20 but I need to be able to add case types without breaking the script.

case_typeslast,_first_name........

View 5 Replies View Related

Shortcut Key Listing

Jan 23, 2009

Need way to list the active shortcut keys? I know there are manyy lists of the desfault shortcuts, buit I'm trying to generate a list of the curreently active shortcut keys & what macros they tie to. I have a vague recollection that someone had a VBA means of doing this, but I cannot find it in the forums I've searched.

View 2 Replies View Related

Listing Data By Category

Jul 25, 2014

I have created a simple account list with - date - detail - category - amount.

How can I automatically list each transaction in date order by category on other pages. Each category is represented by a letter - e.g. 'T' for travel. In this example I want to list all travel transactions in a separate list in date order on another page for easy printing.

I have made a Summary page for each category but cannot work out how to make a detailed list for each category.

View 13 Replies View Related

Finding And Listing Duplicates

Feb 7, 2014

I have a workbook of appointments, I need a formula to list duplicate appointments and display them using 2 criteria (date and time).

See attached workbook : duplicate entries.xlsx‎

View 7 Replies View Related

Listing Values With Exceptions

Feb 28, 2014

To establish the order for on duty personnel every month, I need to find a proper formula.

I have attached the workbook.

So, when I pick out a month from a drop list in cell A1, in column B returns (based on a formula) net work days of that month.

In range H:T, I have a table with the personnel names (in the header) and the individual holidays.

What I want is to have a formula in column C, that returns the name of the first available person (not on holiday in that day), in exactly the same order as it is in the table's header.

Attached File : ON DUTY.xlsm‎

View 5 Replies View Related

Listing A Set Of Files From A Database

Oct 28, 2008

I have one that could be difficult. I have a database with names and corresponding numbers. I am trying to develop a report in which one could choose a name from a list (for example, using validation) and the report pulls out all the corresponding numbers for that name. Not the amount of numbers but the actual list of those that are linked to that name.

View 9 Replies View Related

Pull Out Data From A Listing

Apr 15, 2014

I don't know if this will require a macro or maybe a function of excel, but I have a large listing about 1000 row in spreadsheet. Each row correspond to a unique customer. But I will have to extract only the customer that I am looking for, which is about 30 of the 1000. The cell A1 is the customer # which will be use as reference, so I have about 30 customer # to pull out from that listing. Which will be the greatest method to do so?

Example:
A1 44334 ERIK JOHNSON 60$
A2 34555 KARIM EDWARD 50$
A3 32555 EDWARD CHRIS 65$
A4 55666 STEPH LAWRAW 45$
...
I want to pull 44334 and 55666 the whole row.

View 8 Replies View Related

Pop Up Reminder Listing Cell Then Go To It On Yes

May 15, 2009

Column B on worksheet3 has a list of dates on it. Column C states set reminder. Column D states how many days before reminder i.e. 3, 7, 10, 14. If the reminder is set to on. Can VBA code be run when the Workbook (or worksheet) that checks this criteria then a pops up is displayed outlining the event (say Cell A)?

Also is it then possible to have on the pop up - Go to this event (i.e. row of data?)

View 11 Replies View Related

Drop Down Listing Macro

May 26, 2009

Ok well here goes.

I have an excel book with 2 sheets.

Sheet 1 = dropdown
Sheet 2 = Customer list

What i need is a to be able to add to the customer list on sheet 2 and in the process the customer list to be sorted into alphabetical order.

In addition I need the new customer to be added into the dropdown list on sheet 1.

I know i can extend the list in sheet 2 with blanks so that there is space in the drop down list, but not knowing the number of customers at any one time I have no way of knowing how many blanks to add.

View 12 Replies View Related

Listing Data In ListBox?

Jul 4, 2012

I have a form with 4 TextBoxe's "TexBox1,2,3 etc" and a ListBox1 were the whole idea is that you enter a value in TexBox1 and the VBA code looks for any and all items on your data "respective worksheet" and lists any/all data that it finds "corresponding to the value in TextBox 1" in the ListBox1, just like a database of sorts. Also note the other TextBoxe's 2,3,4 have info in them as well corresponding to the value you put in TextBox1 using the offset function.

What I mean by this is that when ever I enter data in TextBox 1 or 2 or 3 or 4 that the results will be listed in the ListBox1. The reason I need this is, if the info I require for TextBox1 isn't available I can search for the info using the other criteria in the other TextBoxes.

When the code finds entries that relate to TextBox1 it comes up with a message window with the number of entries it found... just in case you were wondering about the code at the bottom of the cmdFind method.

Code:

Dim MyData As Range
Dim c As Range
Dim rFound As Range
Dim r As Long
Dim rng As Range

[Code]...

View 5 Replies View Related

Listing Variables In A Column

Dec 3, 2013

I have a datasheet with 6000+ entries. One column has lists of companies, of which there are around 300 and their are entered into this column multiple times. I organized and identified the largest ones but a work file of 6000 is making it a long process to find every single company listed, paste their name onto a separate table to analyses. Is there a formula to take all the different company names and arrange them in a list OR list them just once in a column next to it? I will have to do this again and the analysis of the companies is the most important.but I find I need to spend all evening steadily combing the haystack for needles!

View 4 Replies View Related

Error Number Listing

Nov 30, 2006

Is there list anyway of all the error numbers and their descriptions?

View 2 Replies View Related

Listing Data Automatically

Sep 25, 2008

I have a Master sheet that lists people Name, address, City, Phone Number etc…

Now I have separate pages for each individual and was wondering if there ways away if I update or change there information on the master page that is would update there in individual pages as well instead of me doing this numerous times manually.

View 10 Replies View Related

Listing Items In List Once

Mar 3, 2010

Is there a way than I can take a list of data and create a new list but only listing each value once if there is a duplicate value within that list?

Original List
=========
Apple
Pear
Orange
Orange
Pear
Apple
Peach
Plum
Pear


New List
======
Apple
Pear
Orange
Plum
Peach

View 9 Replies View Related

Listing Files From Your Computer

Apr 16, 2004

I am attempting compile a list of files from my computer into a spreadsheet but I am not sure how to get the list in the first place--I have searched many, many websites and everyone directed me here...

View 9 Replies View Related

Listing Filenames In A Column

Jul 20, 2006

I have about 50 CSV files in a directory and would like to list them in a column that I can use them to populate a box so that a user can select the file they want in the box and open it in a new spreadsheet.

I have tried several ways to do this but nothing I do works.

At present I am using an imput box for the user to type in the filename, but if they type incorrectly, or type in a file name that doesnt exist the program just exits because of my error trap.

View 3 Replies View Related

Listing Only Items That Pertain To A Subject

Oct 19, 2009

A1: John Black B1: Jane Black C1: John Black D1: John Black
A2: 24 B2: 32 C2: 23 D2: 12


If I choose "John Black", I would like a list generated that lists 24, 23, and 12 in row 3, in separate columns, but no empty columns between the numbers.

View 4 Replies View Related

Listing Data Forms Into Another Book

Nov 7, 2008

I'm using Excel 2003

I have done this once already, so I know its possible but I cannot remember what it is called and how to do it.

I have a "form", I open it up, fill out a few different things, and then save and print it. When I save it I want it to take a few different cells information and report it into a seperate workbook, without having to open the report workbook. I use this seperate workbook to reference when I fill out the form and who it was sent to.

I remember there was a wizard that I used to get it to do all this, but I can't for the life of me remeber what it was.

View 10 Replies View Related

Collect Data Out Listing Xls To Own Workbook

May 13, 2009

we log all customer communication on a daily spreadsheets a report. Multiple projects are listed on that one sheet every day. Is there a way to extract project related communication to a individual project log adding the date from the daily report to the log. The goal is to generate for each project a log will have the daily (or any) communication for one project from the daily report. either adding a work sheet via project name or a separate file. Also is there is a new project a new project log should be created.

View 4 Replies View Related

Listing Cells That Meet A Criteria

Apr 11, 2008

I have a list of items (TR Sets) from 1 to 96 in the range B4:B99, with values corresponding to each one (Acid Number) in the range C4:C99. I would like to make a separate list that names each TR Set (using numbers 1-96) that has an Acid Number > 0.1 . Is there a formula to do this?

View 13 Replies View Related

From A Global Listing To A Packing List

Jun 3, 2009

I am a wood logs trader. Wood logs are being sold per cbm (cylindrical volume). Each log has its own number, length and diameter. Once you bought the parcel, the owner of the forest gives you the listing of the logs. Nothing to it, it's just a list of all the logs with their specifications and numbers. We load these logs into 40' containers. Usually, we measure the logs while loading but not anymore, now the logs have already been measured and are being loaded randomly, hence my problem. Let me give you an example, let's say that I bought a parcel of logs of 100 pieces, marked with numbers from 1 to 100 for a total of 100 cbm. Each log has its own length and diameter of course (like 1 - 10m length - 42cm diameter . 2 - 6 - 52 etc...). The first container has been loaded with 5 pieces: number 6, 15, 52, 74, 75.

My question is, if i input the list of all the logs, would it be possible to create the packing list per container just by typing the log number and letting excel get the length and diameter automatically from the global listing? Because, let me tell you, getting through 2000 numbers to find the length and diameter of each of 15 logs loaded in one container, and thus for 50 containers really ain't fun
I've attached an example of packing list for one container to give you an idea (don't pay any attention to the color, it's just different qualities). I didn't attach any global listing though. The only difference is that it is much longer and in numerical order.

View 3 Replies View Related

Listing All Data From Different Worksheets In One Worksheet

Jan 28, 2013

How to list all data from different worksheets into one worksheet without manually copying and pasting it in a new worksheet?

For Example: I have 20 worksheets. In those worksheets are list of items that I bought from the different stores. I want to make a list of all items that I bought in one worksheet.

View 2 Replies View Related

Listing A Company For Invoicing On A New Worksheet

Jan 7, 2007

for instance "Dave's electric" and only those charges that are between Jan 1st and Jan 5th on a new worksheet. Then do the same for "Girlz Clothing". I know some VBA so I would be willing to go that way too....

View 9 Replies View Related

Listing Conditional Data From An Array

Feb 21, 2009

I have a 125x125 array of numbers each cell of which is equal to the row header divided by the column header. Using conditional formatting in Excel2007 I can visually identify the integer values. However, for each integer value, I want to list in 3 columns the cell value, the row header value (or reference) and the column header value (or reference).

View 9 Replies View Related

Directory Listing: Skips .zip Files

Jun 10, 2006

I'm using a piece of code I found here on Ozgrid to create a list of files in a specific folder. And even though this worked fine for me a week ago, now when I run this same macro, the. zip files are left out of the list, even though I'm specifying msoFileTypeAllFiles. Archive files with other extensions, such as .tar.gz, do make the list. I get the feeling that my zip files are being considered folders instead of files. Do you have any idea why this is happening, or what to do to get that list of zip files?

Sub HyperlinkXLSFiles()
Dim lCount As Long
Application. ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False
On Error Resume Next
With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "o:DataFeeds"
.FileType = msoFileTypeAllFiles.....................

View 2 Replies View Related

Listing All Part Number Combinations

Mar 28, 2007

im looking to piece together all possible part number combinations with a one condition:

The values in Column A must come before the values in Column B. The values in Column B must come before the values in Column C. And So forth...

Example: If Column A had the values of A, B, C; Column B had the values of -1, -2, -3; and Column C had the values of -01, -02, -03

The outcome(s) would be: A-1-01, A-1-02, A-1-03 and so forth...

How can I get it to do that? But I need the flexibility of adding on more "options" should the part number be bigger (for future part number combinations).

View 9 Replies View Related

Listing Cells On Another Worksheet Depending On Cell Value

Jan 7, 2009

I have a list of names in column B and either a 1 or 0 in column A as below:

0 A B
1 1 Bob
2 0 Chan
3 0 Lucy
4 1 Billy

On another worksheet I want to be able to list only the names with a 1 in column A. This must be done in another worksheet so I have something like below.

0 A
1 Bob
2 Billy
3
4

View 3 Replies View Related

Effectively Listing Data From A Table/array

May 7, 2009

I'm currently looking to develop a few formulas that can review individual lines in a table, identify specific values, associate a tag for that value, and then count the number of instances the unique value occurs.

I've had sucess with VLOOKUP and COUNT IF for the back end but don't know where to start on the front end.

In addition I'm unsure whether this is better suited to a Macro since the number of rows created will vary with the data table/array being reviewed.

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved