Posts by davekim1000

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

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