Posts by jindon

    Hi,


    if the data in col.B rows 2 to 53 with heading in row 1


    =INDEX(B2:B53,MAX(IF(B2:B53<>"",ROW(B2:B53))-1),1)
    holding down Ctrl+Shift keys and press Enter to get out of the formula bar


    will return the value in the "bottom-most non-blank cell" within the range.


    jindon

    Hi,


    Here's how it works


    Hi, Phill


    Sorry, I don't quite understand what your friend is trying to do.
    Can't you just use


    WorksheetFunction.HLookup?


    c.offset(Range(gA2h).value=worksheetfunction.hlookup(c.myrange,.....
    ??
    jindon

    Hi,


    Probably, conbination of INDEX and MATCH function would fit.
    where lookup table area is C1:G5


    =INDEX(C1:G5,MATCH(A2,C1:C5,0),MATCH(A1,C1:G1,0))


    Or


    I'm little bit comfused. what the question means is like
    =HLOOKUP(A1,C1:G5,A2+1,FALSE)
    =INDEX(C1:C1:F5,A2+1,MATCH(A1,C1:G1,0))
    =INDIRECT(LOOKUP(A1,{"andrew","bob","john","mike";"e","f","d","c"})&A2+1)
    =OFFSET(INDIRECT(ADDRESS(1,LOOKUP(A1,{"andrew","bob","john","mike";"5","6","4","3"}))),A2,)
    ??
    jindon

    Hi,


    If you are not familier with what firefytr suggested, here's another solution.
    I'm not sure if you are familier with the formula though.


    Assuming:
    Sheet names are Sheet1 and Sheet2 respectively
    ID column in both sheets are in column A and has heading in 1st row
    Extract desired data in Sheet3


    With Sheet3
    1) Set lookup table in range E1:G3
    E1:0, F1: Sheet1!, G1:0
    E2:=COUNTA(Sheet1!A:A), F2:Sheet2!, G2:=E2-1
    E3:=COUNTA(Sheet2!A:A)+E2,


    2) extract all the ID in columnA
    in cell A2
    =IF(ROW()<MAX(E:E),INDIRECT(VLOOKUP(ROW(A1),$E$1:$G$2,2,TRUE)&"a"&ROW()-VLOOKUP(ROW(A1),$E$1:$G$2,3,TRUE)),"")


    3) display row number if value in col.A is unique
    in cell B2
    =IF(AND(A2<>"",COUNTIF($A$1:A1,A1)=1),ROW(),"")


    4) extract unique ID
    in cell C2
    =IF(ROW(A1)-1<COUNT(B:B),INDEX(A:A,SMALL(B:B,ROW(A1)),1),"")


    then select A2:C2 and drag down as you desire


    copy entire columnC and pastespecial/value


    jindon

    Hi,


    OK. what you got is exactly the same result as


    in cell F23
    =if(E12="","",E12)
    simple as above!


    =INDIRECT("E"&ROW()-12)
    also returns the same value


    Check excel help carefully and it is easy to understand.


    Good Luck!
    jindon


    By the way, here's VBA code
    Paste code in sheet module


    Private Sub Worksheet_Change(ByVal Target As Range)
    with target
    if (.column<>5)+(.count<>1) then exit sub
    .offset(12,1).value=.value
    end with
    end sub

    By formula


    data in Col.A and sorted ascending order before insert formula


    B1:
    =IF(ISERROR(--LEFT(A1,COUNT(-LEFT(A1,COLUMN($1:$16))))),ROW()+9^10,--LEFT(A1,COUNT(-LEFT(A1,COLUMN($1:$16)))))


    holding down Ctrl+Shift keys and Enter to get out of the formula bar
    then drag down


    then sort by Col.B


    will give you what you desire, I hope


    Jindon

    If the data begin from cell A1


    B1:
    =IF(COUNTIF(A$1:A1,A1)=1,ROW(),"")
    then drag down to the last row of the data in col.A


    C1: displays unique records
    =IF(ROW(A1)<COUNT(B:B),INDEX(A:A,SMALL(B:B,ROW(A1)),1),"")
    then drag down


    copy entire columnC and paste special/values


    Jindon

    Here's vba codes
    Paste all the code onto sheet module


    '-----------------


    Public lastA As Long


    Private Sub Worksheet_Activate()
    Dim i As Long


    Application.EnableEvents = False
    With Range("n1")
    .FormulaArray = "=MAX(IF((A1:A65535<>"""")*(A1:A65535<>0),ROW(A1:A65535)))"
    .Font.ColorIndex = 2
    End With
    lastA = Range("n1").Value
    Application.EnableEvents = True
    End Sub


    Private Sub Worksheet_Calculate()
    Dim lastM As Long, lastAA As Long, i As Long
    Application.EnableEvents = False
    If Range("n1").Value = 0 Then GoTo e
    lastAA = Range("a65536").End(xlUp).Row
    For i = lastAA To 1 Step -1
    If (Cells(i, 1).Value <> 0) * (Cells(i, 1).Value <> "") Then
    lastAA = Cells(i, 1).Row
    Exit For
    End If
    Next
    Select Case lastAA
    Case Is = lastA
    If IsEmpty(Cells(1, 13)) = True Then
    Cells(1, 13).Value = _
    Range("A" & lastAA).Value
    GoTo e
    Else
    lastM = Range("M65536").End(xlUp).Row
    Cells(lastM, 13).Value = _
    Range("A" & lastAA).Value
    GoTo e
    End If

    Case Is > lastA
    If IsEmpty(Cells(1, 13)) = True Then
    Cells(1, 13).Value = _
    Range("A" & lastAA).Value
    GoTo e
    Else
    lastM = Range("M65536").End(xlUp).Row
    Cells(lastM + 1, 13).Value = _
    Range("A" & lastAA).Value
    GoTo e
    End If
    Case Else
    GoTo e
    End Select
    e:
    lastA = Range("n1").Value
    Application.EnableEvents = True
    Exit Sub
    Application.EnableEvents = True
    End Sub

    Hi, Garcia


    If that a case, data may be "TEXT".


    can you check inserting following formula


    =istext(b194)


    it it returns TRUE then it is text, therefore cannot be calculated.
    I mean it must be converted to the form to be able to calculated.
    Regards,
    Jindon

    If cell A1 of sheet1 has a validation dropdown, then


    try
    cell in sheet2


    =IF(SHEET1!A1="","",LOOKUP(Sheet1!A1,{"AT","BT","MT";"After Taxes","Before Taxes","Monthly Taxes"}))


    jindon

    Solution by formula


    if original data begins from A1 and sorted then


    B1:displays duplicates
    =INDEX($A$1:$A$200,SMALL(IF(COUNTIF($A$1:$A$200,$A$1:$A$200)>1,ROW($A$1:$A$200)),ROW(A1)),1)
    Ctl+Shift+Enter to get out of the formula bar


    C1:displays non duplicate
    =INDEX($A$1:$A$200,SMALL(IF(COUNTIF($A$1:$A$200,$A$1:$A$200)=1,ROW($A$1:$A$200)),ROW(A1)),1)
    Ctl+Shift+Enter to get out of the formula bar


    then drag down both cells


    jindon