Unique Rows / Value with Offset Value

[Senthil Murugan]

Good Afternoon to All

I need unique value from column A with its offset value

can any one let me know the formula or VBA Code

Thanks in advance

A.Senthil Murugan

 

[Peter_SSs]

I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. You will generally get faster/better responses.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

Try this formula.

=LET(u,UNIQUE(A2:A29),HSTACK(u,VLOOKUP(u,A2:C29,3,0)))

 

[Senthil Murugan]

Thank you for your immediate reply Mr.Peter

in this, i am getting N/A in last line as per the image below

can you please let me know

1. why it comes?

2. is there any VBA Code to copy Unique Rows in Sheet1 ( Unique value based on column A ) to Sheet2 ?


with regards


A.Senthil Murugan

 

[Senthil Murugan]

Sorry Mr.Peter

I have data till row 28 but in formula till the row 29

Question 1)

if i take range in formula like
=LET(u,UNIQUE(A:A),HSTACK(u,VLOOKUP(u,A:C,3,0)))
( full column ), how to avoid this N/A error

Question 2)
What is "u" used in formula

Question 3)

is there any VBA Code to copy Unique Rows in Sheet1 ( Unique value based on column A ) to Sheet2 ?


with regards


A.Senthil Murugan

 

[Peter_SSs]

1. why it comes?

Because you have blank cells in the column A range you used in the formula.
You can easily eliminate that with

=LET(u,UNIQUE(FILTER(A2:A29,A2:A29<>"")),HSTACK(u,VLOOKUP(u,A2:C29,3,0)))

2. is there any VBA Code to copy Unique Rows in Sheet1 ( Unique value based on column A ) to Sheet2 ?

Sure, that can be done with vba but couldn't you just put a 'UNIQUE' formula in Sheet2?

If you really want/need vba try this with a copy of your workbook. I have assumed there is nothing already in column A of Sheet2

Sub Unique_List()
  Sheets("Sheet1").UsedRange.Columns(1).Copy Destination:=Sheets("Sheet2").Range("A1")
  With Sheets("Sheet2").UsedRange.Columns(1)
    .RemoveDuplicates Columns:=1, Header:=xlYes
    On Error Resume Next
    .SpecialCells(xlBlanks).Delete Shift:=xlUp
  End With
End Sub

 

[Senthil Murugan]

Dear Mr.Peter

Vow! you are genius . That formula works removing empty row

&

I have altered your VBA Code as below

Sheets("Sheet1").Range("A2:C50").Copy Destination:=Sheets("Sheet2").Range("A1")
  With Sheets("Sheet2").UsedRange.Columns(1)
    .Columns(1).EntireRow.RemoveDuplicates Columns:=1, Header:=xlYes
    On Error Resume Next
    .SpecialCells(xlBlanks).EntireRow.Delete Shift:=xlUp
  End With

Kindly check . It works i think


with regards


A.Senthil Murugan

 

[Peter_SSs]

That formula works removing empty row

Glad the formula worked for you. Thanks for the confirmation.


When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug & copy. My signature block below has more details. I have added the tags for you this time.

Kindly check . It works i think

I will let you do the checking as you are the one who knows just what results you want.

 

[Senthil Murugan]

Dear Mr.Peter

Thanks for your reply

1. Available code tags means the following ?

Sub Unique_List()
xxxxxx
End Sub

2. The Word "as you are the one who knows just what results you want " , are you blaming me?


with regards


A.Senthil Murugan

 

[Peter_SSs]

1. Available code tags means the following ?

No, that was not correct. As I mentioned ..

My signature block below has more details.

.. you need to read my signature block below (the bit that appears at the bottom of every one of my posts) and read the section about posting vba code (the second bullet point) including clicking the link at the end of that bullet point and reading that information too.

2. The Word "as you are the one who knows just what results you want " , are you blaming me?

No again. I am not blaming you for anything. You asked me to check your code because you "think it works". What I am saying is that you can test you changed code and see if it really does work since you know what the results of the code should be.

 

[Senthil Murugan]

Dear Mr.Peter

Ok, Thank you

if did the mistake again, forgive me


A.Senthil Murugan