Lookup?

[sssb2000]

hi, i have a column (A) with a bunch of "K"s in it....scattered through 150k rows.

in column B, i have values for every row.


What formula in C1 will search col. A and return B values for All "T"s in column A?

thanks in advance for any help.

 

[jerry12302]

I would use SUMPRODUCT

Here is an example of it's use:

If you had various fruits in column B, and their sizes in column C, and the quantities in column D, to find out how many medium sized pears you had you would use:

=SUMPRODUCT(--($B$1:$B$100 = "pears"), --($C$1:$C$100 = "medium"), $D$1:$D$100)

Modify to suit your needs, you might only have one criteria, or several more.

 

[brian.wethington]

I am not sure what you are looking for exactly... If you want to only return a value in C if A has a "T" in it then:

=IF(A1="T",B1,"")

will do, and you can drag this down.

If you are looking to sum the values in B if there is a "T" then:

=Sumif(A:A,"T",B:B)

will do.

Do either of these approaches help? If not perhaps you could post some example data with desired results.

 

[Datsmart]

150,000 rows! You must have Excel 2007.
Wouldn't think dragging down formulae into that range would be what you want to do.

 

[sssb2000]

Thansk for the replies guys,
i'm not really trying to sum anything....

In columns A and B i have:

T 5
blank
T 8
blank
blank
blank
T 77
T 9
Blank
Blank
T 90

What i would like to get starting in C1, (down to how many ever populated T's there are), i'd like to have:

T 5
T 8
T 77
T 9
T 90

without any spaces in any rows.

 

[Datsmart]

You can do this manually by selecting the data in column A.
Ctrl-G to bring up Goto dialog box. Click Special button.
Choose Constants, OK.
Ctrl-C to copy.
Select cell you want the data and Ctrl-V to paste.

 

[sssb2000]

Datsmart,
i know about that functionality...however, in my case, it would be alot simpler if i can have a formula that does this. i have over 600k rows of data, and need to have this done automatically several times.

 

[brian.wethington]

hi, i have a column (A) with a bunch of "K"s in it....scattered through 150k rows.

in column B, i have values for every row.


What formula in C1 will search col. A and return B values for All "T"s in column A?

thanks in advance for any help.

A)this explanation did not fit your later data. What was with the "K"? B) I took Values for every row to mean there would be a value in every row of B. C) Then you don't have to physically drag it down (Double click the fill box). D) but why go through all this? Why not just Auto filter? (I am not sure of the limits of autofilter for 2007) or you could use Datsmart's approach with some VBA.

 

[Datsmart]

Don't think you would want to mess with that many formulae.
This VBA code will do what you are asking.

Sub CopyABConstants2C()
    Dim Rng As Range
    Dim c As Range
    endrowA = Cells(Rows.Count, "B").End(xlUp).Row + 1
    endrowB = Cells(Rows.Count, "B").End(xlUp).Row + 1
    endrowC = 1
    If endrowA > endrowB Then
        endrow = endrowA
    Else
        endrow = endrowB
    End If
    Set Rng = Range("A1:A" & endrow)
    For Each c In Rng
        If c.Value = "T" Then
            Cells(endrowC, 3).Value = c.Value & c.Offset(, 1).Value
            endrowC = endrowC + 1
        End If
    Next c
End Sub

 

[sssb2000]

Datsmart,
thank you so much!
i wish my excel 07 allowed me to have macros. it would have made my life alot alot alot easier.
but for some reason, it won't allow me to open VBA editor or record macros. (i've set the security to the appropriate level according to the help file but it's still not working).
thank you so much for the code.