Re: Adding Numbers Together
the countif statement was exactly what I was looking for.
Thanks alot!
David
Re: Adding Numbers Together
the countif statement was exactly what I was looking for.
Thanks alot!
David
hi all,
I am interested in doing a simple vlookup that adds up all the values in a column seperately. Not by number but by the amount of times that value shows up in the column.
Any help with this would be awesome.
thanks,
David Kim
UBS O'Connor Limited
New York, NY
ex.
column
1
2
1
1
2
1
Results:
#1 = 4
#2 = 2
Re: complicated formula
Hi James,
As I am not familiar with how to use pivot tables, I wouldn't know if a solution could be manifested by using them. But thanks for your response.
David Kim
Hi All,
Im wondering if any of you out there can help me create a vb script that does the following:
displays the first and last dates associated with an account number found in a range of account numbers.
If in sheet 1 of an .xls workbook:
Column A Column B
Account Number: Date:
100000 1/1/2005
100000 1/1/2004
100000 1/1/2003
200000 1/1/1980
200000 1/1/1982
300000 1/1/1981
Sheet 2:
If an account Number is typed in Column A in sheet 2, it populate the oldest and newest dates in the appropriate cells in Column B and C that match the account number in sheet 1. Where: If there is only one date, then it treates the only date as the oldest date and the newest date would = "NO ADD HIST"
Column A Column B: Column C:
Account Number: Oldest Date: Newest Date:
100000 1/1/2003 1/1/2004
300000 1/1/1981 NO ADD HIST
Thanks in advance for your help
David Kim
UBS Global Asset Management
299 Park Ave.
New York NY 10171
Re: modifying an existing VB script
Hi Will,
thanks for taking in interest in my problem. so basically, what I want to accomplish is:
sheet 1:
Column A Column B
[email protected] .xls
[email protected] .doc
Sheet2:
[email protected]
if there is a match between whats found in sheet 1 and sheet 2 (column A), then in sheet 3, i want both the matched email address and its description data (found in column B) to show in sheet 3 and the non matches to show up in Sheet 4
Sheet 3:
Column A Column B
[email protected] .xls
Sheet 4:
Column A
[email protected]
thanks in advance for any help you can give...
david
can anyone help me with modifying the below VB script to copy over data found in column B of sheet one WITH the email address that gets matched from the list of email addresses found in sheet 2?
thanks
david
Sub CompareEmails()
Sheets(1).Activate
Dim Sht1Rng As Range
Dim Sht2Rng As Range
Dim cel As Range
Dim k As Range
addr1 = Sheets(1).Range("A1").End(xlDown).Row
addr2 = Sheets(2).Range("A1").End(xlDown).Row
Set Sht1Rng = Range("A1:A" & addr1)
Set Sht2Rng = Range("A1:A" & addr2)
For Each cel In Sheets(1).Range(Sht1Rng.Address)
With Sheets(2).Range(Sht2Rng.Address)
If Not .Find(what:=cel.Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext) Is Nothing Then
If Sheets(3).Range("A:A").Find(what:=cel.Value, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext) Is Nothing Then
FindNewSpotInColumn(Range("A1"), Sheets(3)) = cel.Value
Else
'Do Nothing
End If
Else
If Sheets(4).Range("A:A").Find(what:=cel.Value, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext) Is Nothing Then
FindNewSpotInColumn(Range("A1"), Sheets(4)) = cel.Value
Else
'Do nothing
End If
End If
End With
Next cel
For Each cel In Sheets(2).Range(Sht2Rng.Address)
With Sheets(1).Range(Sht1Rng.Address)
If Not .Find(what:=cel.Value, LookIn:=xlValues, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext) Is Nothing Then
If Sheets(3).Range("A:A").Find(what:=cel.Value, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext) Is Nothing Then
FindNewSpotInColumn(Range("A1"), Sheets(3)) = cel.Value
Else
'Do Nothing
End If
Else
If Sheets(4).Range("A:A").Find(what:=cel.Value, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext) Is Nothing Then
FindNewSpotInColumn(Range("A1"), Sheets(4)) = cel.Value
Else
'Do nothing
End If
End If
End With
Next cel
End Sub
Display More
Edit:WillR - please use the code tags to save someone else having to edit your post, thanks
Re: Working with Ranges to drive data
thanks milyenabox for your help with this!
this worked perfectly.
dave
Hi all,
Can anyone help me write a command button vb script that will allow me to populate column cells with cerrtain content based on the content found in another column.
Where any value within a range of values should populate a cell with certain values.
example:
Column A:
blue
red
green
yellow
purple
orange
if column A cell is value blue or yellow = Column B cell is car / red or purple = boat / green or orange = train
so the results after kicking off the command button script would be:
A1 B1
blue car
red boat
green train
yellow car
purple boat
orange train
thanks for your help in advance.
dave
Re: adding negative values for cells with existing values
When I try to run the macro...
the only thing that I get in return is 0 in cell b2 and b4 of sheet three of the workbook.
can anyone help me modify this script so that on sheet 3 of the workbook both the # value (modified to include negatives where the value is redemption or transfer out) and also the type display on sheet 3?
thanks
david
Re: Using the INDIRECT Function
thanks again TJ for your help!
I'll see if i can't get the VB script you wrote to work for me.
David
Re: Using the INDIRECT Function
TJ,
Actually, I have another wrinkle....
what if i wanted to pull content data via reference flags onto a spreadsheet from multiple workbooks instead of multiple sheets of the same workbook?
the sheet name would be the same in the various workbooks that i wanted to pull from but at the same time, i want to build a marcro that will check multiple workbooks within the same directory folder within my network.
is this beyond the scope of what a macro can do?
thanks
david
Re: Using the INDIRECT Function
TJ,
Actually, I have another wrinkle....
what if i wanted to pull content data via reference flags onto a spreadsheet from multiple workbooks instead of multiple sheets of the same workbook?
the sheet name would be the same in the various workbooks that i wanted to pull from but at the same time, i want to build a marcro that will check multiple workbooks within the same directory folder within my network.
is this beyond the scope of what a macro can do?
thanks
david
Re: Using the INDIRECT Function
TJ,
thanks for your quick reply. it seems to work just fine. However, i was wondering how I would modify the macro to pull data from mulitple sheets instead of just one.
Where:
Sheet 1:
A1: Blue
B1: Car
Sheet 2:
A1: Green
B1: Plane
Sheet3:
A1: Red
B1: Boad
And on sheet 4, I want the results the applicable B1 Cell content if the
applicable A1 Reference is in A1:A3
thanks again for your help
david
Hey All,
How would I create a macro/vb script that would pull content from a cell found in the first sheet of a workbook into another sheet of a workbook based on a reference value associated with the content.
EXAMPLE:
In Sheet 1:
Column A: Column B:
Red Car
Blue Plane
Green Boat
Where: in Sheet 2, I want to create a macro or vb script that will place column B (sheet1) content into a cell in Sheet 2 if Column A (sheet1) reference is found on Sheet 2
Where:
If on Sheet 2:
Cell A1 : Red
Cell B1 : Blue
Cell C1 : Green
Then:
Cell A2: Car
Cell B2: Plane
Cell B3: Boat
thanks in advance for your help
David
Hi all,
this one might be a bit difficult, but I'm hoping someone could help me out!
I'm trying to create a VB script that will add a negative value to a cell if another cell has a certain word in it...
example:
Pre Script:
Cell A1 Cell B1
12345.67 subscription
12347.68 redemption
14423.25 transfer in
17752.32 transfer out
Post "running" the script:
Cell A1 Cell B1
12345.67 subscription
-12347.68 redemption
14423.25 transfer in
-17752.32 transfer out
thanks again in advance for your help with this
david
actually, I have another question...
the formula =countif($i$2:i2,i2) does not seem to copy correctly down the length of the entire column.
where: if row 2, column h =countif($i$2:i2,i2)
shouldn't row 3, column h =countif($i$3:i3,i3)
it actually doesn't in my spreadsheet. I actually get in row 3 column h:
=countif($i$2:i3,i3)
any suggestions on how to easily apply this formula down all of the rows of my spreadsheet correctly
thanks
david
? for u...
what do the numeric values represent on this "count" column?
or in other words...can you help explain what: =countif($i$2:i2,i2) does?
thanks again
david
Wow...
thanks very much for this...i'll test it out now!
thanks again
david
yes...duplicates is what i'm trying to isolate and also aggregate.
thanks
david
Alan,
thanks for your recommendations. I have an additional question though. What I am actually trying to do here is count how many total redundant rows I have total that match via several different column data values.
Any thoughts on how I could achieve this?
thanks again for your advice!
david