how to save the data from a VLook-up?

[Haghir]

I have managed to sort my unique data i have uysed the filter in the sheet to show me the unique companies... I now need to move the 'unique' companies into a seperate sheet/book so I can save as a CSV and upload it to my database... Each time I do this it deletes the info... what am i doing wrong!!!!!

 

[Luke M]

Depends a lot on how you're transferring the data around. Your title has VLOOKUP in it, but your post talks about filters, so I'm confused as to what is actually going on. It may just be that when you move/copy the info, you need to paste special - Values, instead of doing a regular paste with the formulas. If this doesn't solve the issue, perhaps you can elaborate on what is going on?

 

[Haghir]

Thanks Luke... i am not sure either, really struggling... I had TWO sets of DATA,I compared the Data using 'VLOOKUP' to show me the 500 records that were unique or (#N/A) against the 3.5K records I already had. I then used the 'FILTER' button to simply show me the 500 companies ONLY.... I now need to take this VIEW of the 500 Unique records and save them into a SEPERATE spread sheet so i can save it as a CSV File to upload into my database.. I have tried Cutting & Pasting the area I see which is the 500 records I want. However when I do this, it pastes ALL 3.5k records??? any advice... Thank you sooo much!!

 

[Sajan]

Hi,

I am not sure why you are not able to copy the filtered data. (I verified that the copy/paste from a filter works as you would expect.)


As a last resort, you could always copy/paste the data from the VLOOKUP in place, as "values". Then you should be able to sort that list, and delete the rows with the #N/A.


If that still does not help, please consider uploading a workbook (using the guidelines listed on the forum sticky posts).


Cheers,

Sajan.

 

[DaveTurton]

There is probably a much more elegant way of doing things but below is a bit of VBA code which should do what you require.


It assumes the data you are looking up is in sheet1, column A with A1 being the header. The range you are looking up in is in sheet2, column A with A1 being the header. And the output for the unique values being in Sheet3 column A

[pre]

Option Explicit

Sub Extract_Unique_Records()

Dim Search_Data() As String
Dim Search_Array_Data() As String
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim l As Integer
Dim unique As Boolean

'record the Search Data
i = 0
Sheets("Sheet1").Select
Range("A1").Select
Do
i = i + 1
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)
i = i - 1

ReDim Search_Data(1 To i) As String

Range("A2").Select
For k = 1 To i
Search_Data(k) = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Next k

'record the search array data

j = 0
Sheets("Sheet2").Select
Range("A1").Select
Do
j = j + 1
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)

ReDim Search_Array_Data(1 To i) As String

Range("A2").Select
For l = 1 To i
Search_Array_Data(l) = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Next l

'check for unique records and record them in sheet3

Sheets("Sheet3").Select
Range("A1").Select

For k = 1 To i
unique = True
For l = 1 To j

If Search_Data(k) = Search_Array_Data(l) Then
unique = False
End If

Next l

If unique = True Then
ActiveCell.Value = Search_Data(k)
ActiveCell.Offset(1, 0).Select
End If

Next k

End Sub

[/pre]

 

[Lymm]

If you are using the "subtotals" you may need to select "visible" cells only, then paste special values.

 

[Luke M]

Instructions for copy and pasting from filtered list.

1. Select the range of filtered cells

2. hit Alt+; to select only visible cells.

3. Go to destination worksheet/workbook

4. Right-click, paste special. Select Values, ok out.

5. Done!

 

[Haghir]

YOUR a STAR... so all I was missing clicking 'VALUES' when i was pasting it across... Perfect... YEAH... now i can finish it!!!! Thanks once again!!

 

[Luke M]

Yay, glad we got it figured out finally.