Sorting and extracting data... [Solved/Closed]

Report
-
 Saud -
Hello,

I need to extract the data from from the cells of Column "B" and placed it into cells of Columns "E" by comparing Column"A" and Column"D".

 


Sample file attached herewith:

 


https://skydrive.live.com/embedicon.aspx/.Public/test.xls?cid=261a55c976ebb74a&sc=documents

Thanks

1 reply

Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Saud,

What are you looking for in the comparison?
What is the condition which determines to move data from B to E?
By extract data do you mean cut/paste or copy/paste?

Best regards,
Trowa
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Saud,

When using VLOOKUP your matrix (column A:B) MUST be sorted in order to work properly.

Best regards,
Trowa
Hi Trowa,

They are sorted, but still not working.


Regards,
Saud
Posts
2675
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 5, 2020
448
Hi Saud,

Let me state my findings.
1. In your formula you use A2:B931 and then start dragging. This will mess up your matrix. Select the range in your formula and hit F4. This will turn into an absolute reference and looks like $A$2:$B$931.
2. When you double click the cell to go into the formula and confirm with enter, then the formula works for that cell.

If this is true for you as well then you can use the following code to do this automatically:
Sub ActivateFormula()
Dim lRow As Integer
lRow = Range("E" & Rows.Count).End(xlUp).Row
For Each cell In Range("E1:E" & lRow)
cell.FormulaR1C1 = "=VLOOKUP(RIGHT(RC[-1],6),R2C1:R931C2,2)"
Next
End Sub

I have no clear idea why this is happening, my guess would that column A is formatted as text to keep the first 0.

Best regards,
Trowa
I will try it and let you know.

Thanking you
i have tried it with different types of lists and its worked well !!!
its great !!!

thanks you sooo much :)

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!