Adding A Column With Variables For Specific Entries?

Jan 31, 2013

I have a table which looks like this:

Name 1 IDNumber Name 2 Name 3 Column 5

Tom20148 John Malmo
Tom20148 Will Malmo
Bob20206 Will Malmo
Tom20206 Will Paris
Bob20206 Rob Rotterdam
Bob20207 John Rotterdam
Ray20207 John Paris
Tom20208 John Malmo
Ray20208 Rob London
Ray20209 Rob Paris
Bob20209 Will Malmo

Is it possible to have excel go through this list and assign each row a number in column 5 based on the names and the IDNumber? Basically, I would want each entry that is identical in name 1, 2 and 3 to be assigned numbers 1, 2, 3, 4, 5 etc based on their IDNumber. So Tom/John/Malmo with the IDNumber 20148 would get the number 1 in column 5, while the next match (Tom/John/Malmo/20208) would get the number 2 in column 5. For each different match of Name 1,2 and 3, I would want the count in column 5 to start at 1. So Bob/Will/Malmo/ 20206 would get number 1, Bob/Will/Malmo/20209 number 2 etc.

View 4 Replies


ADVERTISEMENT

Adding Dim'd Variables: Ignoring Strings & Adding Number

Jan 13, 2007

I've got a problem involving several Dim'd Variables needing to be added up, they're all Dim'd as Variants though as they can be either strings or numbers at any time.

I need a formula (VBA) to add them up (to add their actual numeric values - not just a "1" if they contain a number) and ignore them all together if they contain string values.

View 9 Replies View Related

Adding Specific Prefix To A Column

May 25, 2012

I need to add a specific prefix (in this case DR- ) to a whole column. The problem is I have some cell that already have the prefix while others don't. I also have some cell with value N/A and I don't want them to get the prefix either

PHP Code:

___C___
DR-1220 
1222
 1233H
DR-1220 
1222
 1233H
[Code] ......

What I need them to be is :

___C___
DR-1220
DR-1222
DR-1233H
DR-1220 
DR-1222
DR-1233H

[Code] ....

The text need to be search able (no formula ).

View 6 Replies View Related

Duplicates Finding Entries In Column A Which Match Any Of Column C Entries

May 7, 2014

What I need to do is sort certain entries in longer list (column A, it is in .csv format and needs to be in it so coordinates and names and ID, all sorted with commas) and I have another list (column C) which is shorter list of certain IDs. I googled and tried and got some results for the basic structure but the fuction seems to fail. It doesn't matter how I get that third list done, but there is only one criteria: since the list in column A is really long and those entries need to keep the .csv formatting, the function should copy that info what is in the matching cells.

Let me try to put it simple: .csv cells from column A that have matching ID from column C should be copied to column B (or N).

the function I'm working with right now is

=IF(ISERROR(FIND($C:$C; A1))=TRUE;"0";A1)

View 13 Replies View Related

Adding Variables To VBA Controls

Jul 17, 2008

I have a userform with a large number of textboxes. Say 100. I want to clear them all after the user submits the data to the spreadsheet. Essentially resetting the userform.

Currently I have:

userform1.textbox1.value = ""
userform1.textbox2.value = ""
...
userform1.textbox100.value = ""

Is there a way to change the textbox number to a variable? Something like:

For textboxN = 1 to textboxN =100 Do
textboxN.value = ""
Loop

View 9 Replies View Related

Adding Prefix To Each Unique Entries In Cell

Jun 12, 2013

prefix.xlsx

I'm finding difficult to find add prefix to unique entries in the column.

For example:
82328331 is appearing 5 times in the column.
so i need to add
82328331A
82328331B
82328331C
82328331D
82328331E.

Likewise i need to add alphabetic series to all the values in the account number column. so i can create unique account number.

Find the attachment : Attachment 242493

View 5 Replies View Related

Adding New Element To A Collection Propagates Added Value To All Entries

Aug 4, 2014

I'm trying to populate a collection with PossibleAnswers to a Question. Every time I do this the values from the newly added entry get propagated to each entry in the collection (best way I can describe it - see code attachement).

Here's the existing code - including the current debugging code surrounding the add! The function is a Method of a Question object.

[Code] ....

All the Find and FindNext procedures do return the appropriate next value. However, the debug loop produces the following:

[Code] .....

As you can see, something is being added, but it seems to be affecting every single entry.

View 3 Replies View Related

IF 3 Variables Then Return A Specific #

Apr 1, 2009

I've attached the example spreadsheet.

I have 3 columns with multiple variable rows.

The 4th column is the specific result for those 3 variables.

I need a formula/s that will give me the correct 4th column # based on the previous 3 columns what would be input using dropdown lists I believe.

So for instance the final end result would be an HTML web page with 3 drop down boxes representing the first 3 columns and then based on what is selected in those 3 dropdowns the correct 4th column # would be shown.

View 14 Replies View Related

How To Get Average Time For Four Specific Entries

Apr 7, 2013

I have a database of over 10,000 entries. I am trying to get the average time for four specific entries (department, test 1, test 2, test 3). I did an averageifs for the tests individually and calculated the average time individually.

The three tests comprise of the department.

Average of test 1 = 40.8
Average of test 2 = 39.9
Average of test 3 = 94.8

Average of dept using the averageifs is 41.3
Average of dept by taking the average of the values above is 58.8

Why is that discrepancy there?

View 6 Replies View Related

Removing Specific Date / Row Entries?

Sep 18, 2013

I have this data set which has customers D.O.B's. This a test data set for the MGM Grand Casino and some customers are under aged below 21 or not even born yet (basically wrong inputs). So ultimately I want to retain the row entries of the customers who were born between (1930 - 1992).

mgm_cleaned TEST  ABCDEFGHIJKLM43928-Sep-20048-Sep-200405.4722000004-Oct-194944969-Sep-20049-Sep-200408.5720.25000004-Oct-1949459320-Apr-200423-Apr-2004010.9255000004-Oct-1949469420-Apr-200420-Apr-200409.2941000004-Oct-1949479121-Apr-200421-Apr-2004019.637.25000004-Oct-1949489221-Apr-200421-Apr-2004016.2941000004-Oct-1949499323-Apr-200323-Apr-2003010.96-10.25000004-Oct-1949509623

[Code] .........

View 2 Replies View Related

Highlighting Duplicates Entries Across Specific Sheets

Jan 20, 2012

I have 13 sheets of data in my open workbook. Column D, which includes blank cells at times, across each of these sheets maps the same info. of orders. However, I know there are several duplicates, and I want to clean them out. I am trying to figure out a way to highlight all the duplicates that appear only in column D beginning with sheet 4 and ending with sheet 13. I will leave the duplicates that appear on Sheets 5-13 alone, but I want to have them highlighted, so I can review the ones that appear on sheet 4 first, and then delete them.

View 1 Replies View Related

Macro To Extract Data From Specific Column To New Sheets Specific Column?

Jun 14, 2013

I'm trying to find a way to use a macro to extract data from a specific column from Sheet1 based on the columns header/title and copied into Sheet2 into the respective column with matching header/title.

For instance, in Sheet1:

Chicken
Cow
Donkey
Pig

[Code]....

So the above table would be the result i'm aiming for.

NOTE, its not different workbooks. I'm looking for sheet to sheet macro.

I've attached a file as well if someone wants to have a go at it. There are no codes in it.

View 3 Replies View Related

Google Spreadsheets - Counting Entries On Specific Date

Jan 13, 2014

How to perform this function on Google Spreadsheets (which appears to be slightly different to Excel);

Calculate the sum of the numbers in column D that occur on the 10th January 2014?

And also, Calculate the sum of the numbers in column D that occur between 9am and 10am on the 10th January 2014.

View 1 Replies View Related

Find Specific Withdraw Entries From Bank Statements

Jan 6, 2010

I use online banking for paying my monthly bills. I get different confirmartion for each pay transection. Pls look attached Excel file. Now I want to find specific bills expense with amount from all my 12 months bank statements. How can I get this task done.

Note: Each time get different confirmation which appears on my bank statement beside paid bill name. for example January 2009 statement GAS Z8A.... Feb 2009 GAS S2W. I hope this clarify. What I have to do if I want to get all my 12 months GAS money withdraw from bank statement. I use csv file for bank statement.

View 11 Replies View Related

Identifying Unique Entries Based On Unique Entries In Another Column?

May 29, 2014

I have a list of data and I want to identify the unique entries for both columns but the second column has to unique to the unique values in the first column.

Example List

Fruit
Color
Apple

[Code]....

View 9 Replies View Related

Speed-up Slow Macro Code: Entries In Column 73 - 85 Will Be Copied From WorkbookRust To The Other Workbook If The Numer In Column 5 Is The Same

Mar 25, 2008

my existing macro, as the run takes to much time to complete. (at least 20min) I've already tried several loops, but no one worked for me. Following situation: There are two excel files, entries in column 73 - 85 will be copied from WorkbookRust to the other workbook if the numer in column 5 is the same. Not every cell within this columns contains data, so the macro should automatically jump to the last entry in each of the above mentioned columns, instead of predefine the range as you see in the code below. After the data is copied to the other workbook, it will be filtered acc. to Sub FilterMain and then copied back to WorkbookRust. As already said, the whole thing works, just to lame.

Sub Allmacros()
Dim WorkbookRust As String
WorkbookRust = ActiveWorkbook.Name
ChDir "C:Documents and Settings vogtMy DocumentsRüstplausch"
Workbooks.Open Filename:= _
"C:Documents and Settings vogtMy DocumentsRüstplauschCH_Revenue_2008.xls"
Sheets("Main_Overview").Select
Windows(WorkbookRust).Activate
Application.run ActiveWorkbook.Name & "!UpdateEntries"
Application.run ActiveWorkbook.Name & "!FilterMain"
'not ask to overwrite existing file
Application.DisplayAlerts = False
Workbooks("CH_Revenue_2008.xls").Save
Workbooks("CH_Revenue_2008.xls").Close
End Sub

Sub UpdateEntries()......................

View 7 Replies View Related

Adding Data From Specific Names

Dec 17, 2008

I have a sheet that has the same employee names several times in different orders in the same column with data to the right of it. Example

Name.......Pieces...hrs
.....A........B........C
(1)John...1000......12
(2).........2000......20
(3)Jay.....2000......31
(4).........2500.....20
(5)John...2000.....50
(6).........5000.....60
(7)Bill......1200.....40
(8)..........3000.....60

I need the peices and hours total for each name on another sheet. So I would have John on my sheet and would need to to grab and add the info from B2 & B6 into one cell (since it is the same person). I can always drag the info over for hours once I find a way to do this for the pieces I would think. The problem is that I don't want to add in B1 & B2 for John because those numbers are not a part of the total.

So is there a way or formula for one cell to look at the entire sheet and everytime it sees the name john to add the information one column over and one cell down and then give a total?

I may be able to do some formatting and have all the info I need directly to the right. So I would have (A1) John, (B1) Data, (C1) Data. The issue would be a cell finding the name, taking the information directly to the right of it and adding it as many times as the name is found on the sheet.

View 5 Replies View Related

Adding Cells Over Specific Range?

Sep 19, 2013

I'm looking to easily drag the sum of certain cells in a different column BUT keeping a specific range, it's hard to explain so i'll show an example...

A1
A2
A3
A4
A5
A6
A7
A8
B1=SUM(A1:A4)
B2=SUM(A4:A7)
B3=SUM(A8:A11)

And so on...

Is there any way I can do this by dragging down the cell formula from B1 and it remembering the range of 4, so I don't have to manually select each range...?

View 1 Replies View Related

Adding From Specific Named Cells

Jan 21, 2009

I have 2 columns named "ASC" and "AE" which have total calculations of stores inventory data. To the right of the "ASC" and "AE" columns are store columns with (C1="store#"), (C2="state"), (C3="name"), and (C4:C14="inventory count") totals.

If at anytime a stores "name"="AE", I want the "inventory count" for that store to calculate within the the "AE" column.

Anytime a stores "name"="anything except AE", I want the "inventory count" for that store to calculate within the the "ASC" column.

A1:A3= "ASC"
A4 through A14= Inventory Total

B1:B3= "AE"
B4 through B14= Inventory Total

C1= Store#
C2= State
C3= Name
C4 through C14= Inventory count

D1= Store#
D2= State...

View 11 Replies View Related

COUNT 2 Variables In One Column Against Another In Separate Column

Jun 17, 2013

I want a formula to do the following. Count all instances when column A contains S or A and column B = 1. The example below would equal 2 (ONES MEETING CRITERIA ARE IN BOLD).

Example

N 2
S 1
A 1
D 3
X 2

View 2 Replies View Related

Adding Specific Values In Different Columns Using One Formula?

Mar 28, 2014

How would I go about finding the "Number of Shirts Ordered" values in the top right?

View 1 Replies View Related

Adding Years To A Specific Year And Then Rounding It Up

Apr 3, 2009

There are two columns in an excel sheet, one is date of birth and other is date of reteirment

looks somewhat like this:

D_O_B D_O_R
5-Mar-53 31-Mar-11
30-Jun-57 30-Jun-15
20-Jun-51 30-Jun-09
2-Feb-55 28-Feb-13
2-Jul-51
13-Oct-55
1-Sep-51
7-Jul-54
14-Mar-53
3-Aug-50 3 0-Sep-13

Some of the dates in D_O_R are missing. I need to fill in up all the dates in D_O_R column.

D_O_R is = 58years+D_O_B(NOTE the dates should come as last date of the month)

View 8 Replies View Related

Autofill Column B Based On Column A Entries

Oct 9, 2009

I have a Listbox that outputs data to Column A. Is there a way to auto populate Column B with 4 items for each Entry in Column A?
The 4 items that will populate in Column B will always be the same.

I have attached an example to better explain what I am trying to describe.

View 6 Replies View Related

Function To Sum A Column's Entries Based On Another Column

Feb 20, 2009

Let's say I have a dataset with blank or X in column A, and a dollar value in column B. I want to sum the dollar amounts for column B only if there is a corresponding X in that row in column A. The way I currently do that is by creating a new column C, making a if statement to display the value of B only if there is an entry in A, and then doing operations on that new range. I figure there has to be an easier way. I don't know how to use VLOOKUP, despite trying to read the helpme a bunch of times. Is that relevant?

View 8 Replies View Related

Adding Variable To Specific Sheetname In A Sumif Function

Feb 4, 2010

Hi, looking for help desperately in fine tuning a formula. I have a formula at the moment (which works) for searching through a list on a separate file and totalling up all values which relate to it, see below:

=sumif([filename.xls]1’!$B:$B,D10,’[filename.xls]1’!$H:$H)

The tab ‘1’ in the formula relates to the first of the month so this month there are 28 different tabs with similar information.

With C10 containing the date in this instance, does anybody know a way of making ‘1’ a variable so that entering ‘04/02/10’ would change it automatically into a 4? (Unfortunately for me changing the 1 to =c10 didn’t work).

View 14 Replies View Related

Adding An Auto Filter On Specific Columns Of A Worksheet..?

Dec 14, 2007

How do I go about using adding an auto filter on specific columns of a worksheet..?

I.e. I want to auto filter column "D", "G" and "I" but none of the columns in-between ("E", "F" and "H")

Currently I can only create the filter for one column or a group of columns that are next to each other)

View 9 Replies View Related

Timeline Adding Specific Time Frame In Each Additional Cell?

Jun 23, 2014

Rather than manually typing the time in 5 min increments across a range of cells, i want to have a start time, then the next cell add 5 mins. EG: 09:00 next cell 09:05 and so on.

View 7 Replies View Related

If Specific Row Contains A Word Then Clear Specific Content In Corresponding Column

Jun 18, 2014

I have a row (will always be row 3) where each cell contains a day of the week, the days repeats for a year or so, making the row almost 400 cells.

Like this,
Mo - Tu - We - Th - Fr - Sa - Su - Mo - Tu - We - Th - Fr - Sa - Su - and so on...

Though, A3 doesn't have to be "Mo" because the days in this case can change (A3 can start with "Tu"), hence I think I need a macro.

So if this row contains a weekend, "Sa or "Su" I want all the cells in the column beneath that which contains a specific value to be cleared.

Example, if "Sa" or "Su" has 3 values in the columns under them, all the values that contain "X" or "Y" has to be cleared.

Like this:
Rows (1,2,3...,)
1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su - .. and so on..
4 A --- B --- X --- Y --- X --- B --- Y
5 A --- B --- X --- Y --- X --- X --- X
6 A --- B --- X --- Y - --X --- Y --- C

After the macro it should be:

1----
2----
3 Mo - Tu - We - Th - Fr - Sa - Su
4 A --- B --- X --- Y --- X --- B ---
5 A --- B --- X --- Y --- X --- ---
6 A --- B --- X --- Y - --X --- --- C

Notice the two examples in the excel file.

Excelforum.xlsx‎

View 6 Replies View Related

Delete Rows With Specific Characters In A Specific Column

Dec 10, 2007

Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):

Find_Range("A", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("B", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("C", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("D", Columns("J"), MatchCase:=True).EntireRow.Delete
Find_Range("E", Columns("J"), MatchCase:=True).EntireRow.Delete

although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:

Range("1:65536").Select
For Each cl In Range("J:J")
If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then
Rows(cl.Row).Delete
End If
Next

but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...

View 5 Replies View Related

How To Sum Variables In Column

Dec 2, 2013

I am making a weekly plan for machine inspection at the end of every column i need to sum how many machine will be inspected.

L= area
L1=10 ,) L1 has 10 machines ... this number can be change in the future that is why t is variable. for example:
L1=10, L2= 25, L3=20
week1
L1
L3
sum ?

View 9 Replies View Related







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