Output Tally Of Multiple Columns

Aug 6, 2007

I have been working on a little vba project and have almost got there with help from others, but the final part needs to have a tally a number of columns and and produce the tally results to a new worksheets - the code that need proof reading /reviewing is below - it is not working correctly as it is not tallying correctly and at times seems to miss counting the last row - so could you review and suggest or make required changes so that it will work on the attached test file.

WHAT IS NEEDED IS;

1. Column D contains names -strings which are repeated such Fred Flintstone so for Fred and others would like to have a summary tally of how many times each worker has instances of a value appearing in columns I, J, K, L and P, R ,S, T and U.

With the output summary worksheet called "WORKER TALLY" and having the headings for each columns tallied from row 5 being headings for each column tally see attached example.

2. Then do same again for Column E - Names so for example the name Peter Pan and others would like to have a summary tally of how many each worker has instances of a value appearing in columns I, J, K, L and P, R ,S, T and U. With the output summary worksheet called "Names" and having the headings for each columns tallied from row 5 being headings for each column tally see attached example.



Sub workertally()
Dim b() As Variant
Dim NewWs As Worksheet
Dim j As Integer, i As Integer
Dim a As Range, v As Range, r As Range, c As Range
j = 1
Set a = Range("E6", Range("E" & Rows.Count).End(xlUp))
Set c = Range("E5", Cells(5, Columns.Count).End(xlToLeft))
With CreateObject("Scripting.Dictionary")
.CompareMode = vbTextCompare

View 9 Replies


ADVERTISEMENT

Output Data From Multiple Columns / Remove Duplicates

Apr 4, 2013

This is a very SMALL sample of the data I need to sort through. We have been using filters but the data is growning and becoming far too time consuming using the filter method.

The output I am looking to achieve is something like this, (any format is fine; whether in rows or columns)

SVR1
1.99.99.254
(2) CAT I (display how many CAT I for SVR1)
CAT I: 2011-B-44
CAT I: ST-5546
(2) CAT II (display how many CAT II for SVR1)
CAT II: 2011-B-52
CAT II: 2011-A-21

SVR2
2.5.2.333
(1) CAT I
CAT I: 2012-E-55
(1) CAT II
CAT II: 2011-A-21

COLUMN A, varies from SVR1-SVR1400 (cointains duplicates)
COLUMN B, IP matches SVR name (contains duplicates)
COLUMN C, will be either CAT I, CAT II, CATIII
COLUMN D, will contain duplicates

I have some history of using formulas and nested formulas but this one is really throwing me for a loop. It feels like Inception to me and I'm not really sure where to start!

A
B
C
D

SVR1
1.99.99.254

[Code] .....

View 9 Replies View Related

Finding The Matchs In Columns A,B,C And Gives Output In D & E

Sep 30, 2009

Could someone please help as I need to get the totals and whether its negative or positive balance.

To be more specific in a workbook in sheet1 I have Client data columns A,B & C in A,B,& C should be match with the companys data in the next sheet.

If this both sheets sheet1 & sheet2 and columns A,B,& C is matched then in column D in both the sheets should be Matched and Column E should be 0 in both the sheets.

And,The problem starts when both the sheets are not matched if the Column A,B,C is not matched in Column D it should show as "NOT Matched " and difference in Column E in both the sheets.It may be positive figure in one sheet and Negative figure in another sheet.

Please advise If any Forumulae which can help me to sort this out or should I need to go for an Macro.

Please find an sample sheet is attahced.

View 13 Replies View Related

Cross Check Two Different Columns And Output Common Entries?

Jul 12, 2012

So I have two columns, in column a, i have a list of zip codes and in column b i have a larger list of zip codes. is it possible to find what zip codes in column a appear in column b, and then output those zips?

View 1 Replies View Related

Multiple Ifs With Corresponding Output

Feb 27, 2014

I'm trying to create an If then statement that will will allow multiple criteria and output the corresponding data if possible.

I've attached a sample data file to provide insight. I've also used a random number generator formula to create the numbers for the output. When I press F9 I'd like the data to appear in the dream house lottery section with the information of the house that corresponds the Neighborhood and street. What I have in mind below.

If C21=B2,B8,B13 and C22=B3,B4,B5,B9,B10,B11,B14,B15,B16, Then values of E,F,G,H,I appear in E,E,G,H,I appear in the lottery dream house section.

So =IF(C21=B8),IF(C22=B11), ????
IF(C21=2) and (C22=3), Then This is where my problem occurs. I'd like my output to be that of E through I11 to appear in E through I21.

Is that possible and does that make sense. I have a long winded version but it requires the formula to be in each and every cell and it will only allow for one neighborhood at a time.

View 9 Replies View Related

Formula To Compare 2 Columns And Manipulate The Resulting Data And Output

Mar 31, 2007

I have column A and column B:

I will be inputting data into column B.

I need excel to check to see if the data I input into column B is an exact match to the data in column A.

If it is an exact match, then column B will remain blank.

If the data in column B is different, I need column B to show the following:

No match: <data>

Example I input in column B the following:

Column A Column B
1. Car Car
2. 4357 9999
3. fsd34d 4erd
4. 98dkf 98dkf

Spreadsheet should show:
Column A Column B
1. Car
2. 4357 No match: 9999
3. fsd34d No match: 4erd
4. 98dkf

(Cell 1 and 4 in column B are empty because they are exact matches to Column A cell 1 and 4)

My questions:
1) How does the excel formula need to be written for this to work?

2) Is there a way to set it so that when I do a mass copy to data into column B that the formula will not be overwritten and it will still check to see if the data I copy and pasted into that column matches the data next to it in column A?

View 14 Replies View Related

Multiple Output From Lookup

Dec 25, 2012

Writing a formule to lookup for a value and return more than one result,

Example

Col A has Names, Col B has ID number, in Col C I need the output to look for names in col A and return the ID from Col B, I can use a VLOOKUP for this but the problem when col A has a name repeating more that once, In this serinario the lookup picks the first name and gives the first ID

Here is how I want the out put to be as:

Names ID Output
John 1 1,3,6
Mathew 2 2
John 3 1,3,6
Mike 4 4
Bob 5 5
John 6 1,3,6

The output should have the ID of all same names

View 7 Replies View Related

Excel 2003 :: Compare Characters Between Two Columns And Output Results In Same Worksheet?

Feb 6, 2013

I have a worksheet with five columns (A, B, C, D and E)

The cells in Column B contain letters and/or numbers (without spaces) in no particular order.
The cells in Column C contain letters and/or numbers (without spaces) in no particular order.

I want to compare all characters in 1st Cell of Column B with all characters in 1st Cell of Column C, and display the matching characters in 1st Cell of Column D, and the character count of 1st Cell in Column D must be displayed in 1st Cell of Column E. note that multiple instances of the same character must not be treated as duplicates. When execution on 1st Row is finished then repeat procedure for Row 2, etc... Stop execution when first empty cell in Column B is located.

Example:

B1 = LJLM12
C1 = KY2MLK
B2 = ZCG4GM
C2 = X4GGGC
B3 = KTCBNG
C3 = GNBTBB

The script/code/formula must output the following:

D1 = LM2
E1 = 3
D2 = CG4G
E2 = 4
D3 = TBNG
E3 = 4

I am using Excel 2003. Y

View 2 Replies View Related

Multiple Output From Any Cell Input

Sep 18, 2006

Im working a spredsheet for my work, it is a database of qualifications for certain employees. There are about a hundred different qualifications and about 20 different employees. And some of the qualifications overlap. I would like to input a certain date in one of the cells, and have it copy into some of the other cells where the quals overlap. But i would like to input that date into any of the cells that overlap, not just one.

View 12 Replies View Related

Excel 2010 :: Compare 2 Columns In Different Sheets - Unique Values Output To 3rd Sheet

Feb 4, 2013

I'm trying to compile a VBA that would allow me to compare 2 columns "A" in different worksheets (same Workbook) and output any unique values to 3rd worksheet together with the rest of the values in the corresponding row.

Sheet1
A
B
C

[Code]....

Excel 2010

View 9 Replies View Related

Multiple Reference Lookup And Output Sum Of Corresponding Values

Apr 3, 2013

language
batch
WC
FC

zh
1
36
0

id
1
0
0

[code]....

in the above table I need to the sum of WC for each Language code & batch no. eg. for Language "id" & batch 1 I need to get the SUM of WC corresponding to the criteria.

View 2 Replies View Related

VBA Array VLookup And Multiple Results Output In One Cell

Oct 21, 2012

What I need is a script or formula with returns multiple results and puts them into one cell.

The Excel is used as a Project Managment Work Sheet for Resources, working on different Projects and their dedicated hours

The raw data table looks like this:
Column A: Project Name (=AllProjectsLists)
Column E: Resource Name (=MasterDataResources)
Column P: values from 0 to 8 (hours)

First I need to check per row the Resource Name. If the Resource Name is a match, than I need to check if in column P the value is bigger than 0. The result goes into a different sheet per row, one result per Resource, but the multiple results should be shown in one cell.

So the result should be something like:

Resource name "Thomas" .... Projects working on: "Project 1, Project 5, Project 13, ..." (in one cell)
Resource name "Mary" .... Projects working on: "Project 3, Project 9, Project 13, ..." (in one cell)

I tried with this one, but it only returns one vale per cell:

=SMALL(IF(E$9=MasterDataResources; ROW(MasterDataResources)-ROW($A$2)+1), ROW(1:1))
=INDEX(AllProjectsLists; SMALL(IF(E$9=MasterDataResources; ROW(MasterDataResources)-ROW($A$2)+1); ROW(1:1)))

View 5 Replies View Related

Recognise Words In Multiple Cells With A Unique Output?

May 9, 2014

in column A i have fruit words (e.g. apple, banana, orange...)

in column B i have cities (e.g. london, paris, rome...)

i would like a formula in column C that gives "british apples" when "apple" and "london" are on the same row
whilst also giving "french bananas" when "paris" and "banana" are on the same row.

View 10 Replies View Related

Spreadsheet - Output Choices / Multiple Dropdowns In Sequence

Mar 14, 2014

I am looking to create a spreadsheet wherein the user can choose multiple drop downs in sequence and then have it output each choice to a template or empty space within the spreadsheet.

For example, lets say column 1 said what is your favourite colour and had a dropdown with two choices red and green. Column 2 says "favourite place" and had a drop down with two choices "London and USA". Then once the user chooses an answer in each column it would output these choices to notepad or a large space within the spreedsheet.

In this example it would output:

"Red
London"

if the first two answers were chosen from each column. Obviously it would be a much larger scale than the simple example above.

View 1 Replies View Related

VBA To Output Column Of Dates Within A Range For Multiple Ranges

May 21, 2014

I need a 'simple' vba that outputs all the dates in a range that correspond to a particular ID in a column as well as that same ID in a column beside the dates.

So basically using the data in columns A to B as input, to get columns E and F as output.

Also, for a lot more than just 3 IDs.

View 3 Replies View Related

Multiple Input Boxes & Output To Specific Cells

Jan 27, 2007

> When the workbook is opened I want a box to appear with a message and 4 choices (as buttons?).

> Based on the button clicked I want 1 of 4 new boxes to appear (replacing the first box) and ask for input values.

>I then want the inputs to be written to specific cells on a specified worksheets inside the workbook.

View 2 Replies View Related

Tally Score

Mar 14, 2007

im using spreadsheet works which seems to be very similar to excel. i am making a table full of numbers and i want to count how many times the number 1 appears and for that amount to be displayed.

View 11 Replies View Related

Importing Data From Tally ERP 9

Jan 29, 2014

I have been asked to develop a dashboard for my company. The data source for my dashboard is Tally ERP 9.The problem is I am not getting how to access this data into excel. I have tried MS Query but the imported data was not what I was expecting.

View 1 Replies View Related

Keep A Running Tally On Sheet 2

Nov 26, 2007

i am currently trying to figure out excel i have to fill this worksheet out daily and then at the end of the month i have to count the total number of items. I was wondering if there was a way to auto talley on a different sheet so that everytime i entered a product name and quantity if on the other sheet it would auto add it in
example:

I ate at mcdonalds (Main sheet)

On 11/22 I had 3 big macs and 4 quarter pounders,
On 11/24 I had 5 Big Macs and 2 quarter pounders
On 11/26 I had 12 Bigmacs and 5 quater pounders
(New Sheet) i want it to look like this:

Product quantity
Big macs (20) <-- this number i want to auto add from the notes made from main sheet)

So on another sheet i want to make a formula where for everytime i type in "big mac" on main sheet that on the other sheet it would auto add or keep a running tally so after i made those 3 entries on the main sheet it would show that i had a total of 20 big macs on the new sheet.

View 9 Replies View Related

Keep Tally Of Team Scores

Jan 19, 2008

I need to create a sheet probably involving macros where I enter a numerical value into a cell (say B5) I want the sheet to assign a score to the value I enter and then add the score it generates to a table of values. Once ths is done I need it to automatically clear the original value I entered ready for me to enter a new value - rinse repeat..

e.g.

into B5 I enter 15 - on the right in the results table there are headings v w x y and z

say 15 scores one point for w and one point for z I want the sheet to add 0 to the totals under v,x & y and add 1 to the totals under w and z.

Then I want the sheet to clear cell B5 so I can enter a new value without manually deleting the previous entry.

If I can also copy the value I enter into a "history" which simply lists all the values I enter as I go along so much thebetter.

View 3 Replies View Related

Find And Match And Tally With 2 Workbooks

Jun 11, 2009

I have 2 workbooks. and i would like to do the following:

Workbook1

- 4 columns (product ID, quantity, Group, Comments)

Product ID, Quantity, Group, Comments
11111, 500, ?,?
11122, 1000, ?,?
11133, 250, ?,?
11144, 250, ?,?
11343, 700, ?,?
12134, 750, ?,?

Workbook2

- 2 columns (group A, Group B)

for group A:
min qty: 250
min increment: 250........................

1) I need to match the product ID in workbook1 with workbook2 - see which group it belongs and put into workbook1 under column group

2) I need to count the no.of quantity and see if it meet the citeria and place Yes/No in workbook1 column under Comments.

- for example:
product ID 11111 in workbook1 can be found in workbook2 group A (put group A into workbook1 under column group)

do a check: prodct ID 11111 has 500 in quantity, since its under group A it meets the min qty of 250. and followed by it meets the min increment too - 500/250 (thus put Yes into workbook1 column under Comments)

View 9 Replies View Related

Tally Count Of Values In Column

Dec 15, 2007

I have a list of varying IP addresses in a column. I need to create a formula that will tell me how many times an IP address appears in the column so that I can ultimately determine the most common IP address listed.

View 6 Replies View Related

Word Text +1 Tally With Cell Placement

May 28, 2014

I am looking for a formula or multiple formulas to perform an If/Then statement, adding and dividing. Example listed below.

If O8 = 'Y' then perform XXXX, otherwise, do nothing.

XXXX= in Y8 search for word 'apple' and add tally to AA46, in Y8 search for word 'orange' and add tally to AA47, in Y8 search for word 'banana' and add tally to AA48.

Take AA46/Y46 and put results in AB46.

If this could be put into on cell and perform all the needed actions, otherwise, multiple cell formulas is ok.

View 10 Replies View Related

Vlookup: Tally Casino Trip Results

Aug 14, 2009

see the attached spreadsheet...I have a few notes of what I want to do. I think it is a vlookup, but i can't figure out how to do these.

View 2 Replies View Related

Numbers And Text In Same Cell & Tally In A Formula

May 10, 2008

Can numbers and text be included in the same cell and still have the number be included in the total in a formula in another cell? Or must a cell only have numeric values for it to be seen/included in a formula's total value.

I'm trying to create a database that totals materials for a construction project. I want to display the number of doors for a house in a row of cells and have the all the doors totaled in the last cell. This I have no trouble doing.

The problem arises when I want to add some text information about the style of each door in the same cell that the number of doors is shown. As soon as text information is added to a cell that has numeric information, that cells numeric information is not included in the final total in the last cell in the row.

I resorted to using comments instead, but, when the are made visible on the spreadsheet, they don't seem to lock to a relative position regarding the cell they're attached to. For instance, if I widen columns or make any significant spatial changes to the spreadsheet, the comments don't move with the changes.

There may be a way to lock comments to stay in a relative position regarding the cell they're attached to. And if that's the only way to make comments for the items in each cell stay with the cell, then I'll have to use that method. But I'd rather not have to use the comments function at all.

I'd much rather be able to have numbers and text be in the same cell, and still have the number value of that cell be included in a formula total at the end of a row of numeric information.

Example: (In this example separate cells that include both numeric values and text are indicated by parenthesis. The final cell that has the formula that totals the numeric information in the separate cells is indicated by brackets)

(30, raised panel doors, unpainted) (10, raised panel doors, white)

View 9 Replies View Related

VBA: Tally # Of Times A RTD Referencing Function Takes A Specified Value

Jul 31, 2009

I have a cell, lets call it A1, w/ an IF statement referencing another cell, which is a function of two values taken from RTD. A1 is either blank or reads "One." I want another cell to tally the # of times that A1 takes on the value "One."

The issue:
I've tried approaching this by using Worksheet_calculate and an IF/Then statement to add 1 each time Range("A1") = "One" however this doesn't work because it keeps adding 1 while A1 reads "One." The calculations seem to fire off every milisecond, so if "One" flashes for 0.5 seconds, I get 500, and if it flashes for 3 seconds, I get 3000. For both cases, I'd like it to just add 1.

View 9 Replies View Related

Generate New List And Tally Non Blank Cells

May 7, 2007

I am trying to make a more useful excel extracted report – what I have to start with is muck like the attached sample file. With at the bottom is what I would like to be auto done.

There is a column of names – with most names repeating so the first thing needed is to create a new summary list – can be in the same worksheet or in a new worksheet. The new list with be just a list of each name but only display each name once rather that the multiple of times as source list.

The second requirement is than once have a list of each name that appears then in columns next to each a tally /count of each non blank cell under each respective column heading - now it has to be a count of non blank cells as the content of each cell will chance – now need to understand it is not a count of numerals as such but a count of non blank cells so it is not 1 + 3 + 5 = 9 but should be 1 +3 + 5 = 3 (3 non blanks).

A idea of what I want is at bottom of sample worksheet attached

View 9 Replies View Related

Create A Tally Sheet To Keep Track Of My Inventory Of Inserts

Aug 22, 2009

I am trying to make a tally sheet to keep track of my inventory of inserts. I am trying to make it as user friendly as possible as my operators do not have much experience working with computers. I will attach what I have made so far. The only math functions so far are: C4=B15-C15.

Right now the operator has to look at C4 to see current total, type that number into B15, then in C15 type the number of inserts thrown out to show a new current inventory total in C4. Is there a way I can set this up so that all an operator has to do is type in only the number of inserts thrown out in C15 to give current total in C4. Is there a way to make B15 know what is in C4 without the operator having to type it in. If so is there also a way to make C15 the only cell that can be edited.

View 3 Replies View Related

Tally All The Work Orders That Are Created And Either Completed Or Pending

Aug 16, 2008

Her situation is that she would like to be able to tally all the work orders that are created and either completed or pending. According to what she tells me she usually spends hours tallying the monthly worked on orders manually....

View 15 Replies View Related

Tally Cell Automatically Based On CheckBox State

Jan 18, 2008

In my spreadsheet the user has the option to put in a numerical value into different cells, which will update (add to) another. So if we have an initail value of 20 in "I10" and the user puts in 20 in "W12", then I10 will display 30.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("W12:X24")) Is Nothing Then
Select Case Target
Case Is = Range("W12")
Logbook.Show
Range("I10").Value = CInt(Range("I10").Value) + CInt(Target.Value)
Case Is = Range("X12")
Logbook.Show
Range("J10").Value = CInt(Range("J10").Value) + CInt(Target.Value)
Case Is = Range("W13")
Logbook.Show.................

View 6 Replies View Related







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