# Posts by jindon

Hi,

ActiveWorkbook.Names(vInput2).Delete

I hope it works

jindon

Hi,

If ActiveCell.Column=2

is it?
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,

I'm not sure if the attached is what you desire.

## Files

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)
??
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

Hi Garcia,

Would you like to upload smaple file?

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

Hi,

Try

Sub tab_name()
Dim ws As Worksheet
For Each ws In Sheets
ws.Name = ws.Range("d3").Value
Next
End Sub

Hi,

in cell F25
=IF(OFFSET(F25,-12,-1)="","",OFFSET(F25,-12,-1))

and drag down as you want

hope it works for you

Hi,

=IF(MOD(ROW(A1),4)=1,B6-B5+IF(B6<B5,1))

I guess you want

=IF(AND(B5<>"",MOD(ROW(A1),4)=1),B6-B5+IF(B6<B5,1)),"")

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