I would like to match the data in one column to the data in another column.
One column has 3500 rows with 5 cells of data in each row that also needs to be moved. and the other one has 84000 rows with 25 cells of data.
I would like to match the data in one column to the data in another column.
One column has 3500 rows with 5 cells of data in each row that also needs to be moved. and the other one has 84000 rows with 25 cells of data.
using a vlookup function is easier for beginner, try to google it.
the result of #N/A mean there have no search result in another data
weakness is may cause a bit slow to calculate.
anyway, upload a simple file will get more help to direct get a formula....
Hope you can learn every time you visit here.
If you still confuse on how it work, kindly ask or go to
i) Formula - Formula (Ribbon) > Formula Auditing (Section) > Evaluate Formula > Evaluate; or
ii) VBA/Code - Click F8 to see how it work step by step.
It it take care of your question, Please:
Mark tread as [Solved] [Thread Tools->Mark thread as Solved];and
Click *Add Reputation to thank anyone solved your question.
Okay, I uploaded two formulas. They partially do what I want.
=IFERROR(IF(MATCH(A2,D$2:D$6,0),A2,""),"")
=IFERROR(VLOOKUP(F3,D:E,2,0),"")
It gives a return matching the number I am looking for, but, I need the data in three more columns to populate the data in the adjacent cells.
=IFERROR(IF(MATCH(A2,D$2:D$6,0),A2,""),"")
This formula will not work......
Match only return number, not true and false, the answer sure 100% be "" and it due to iferror,
Upload a simple file mean upload an excel file.....
In 10-20 row that can represent the whole workbook that have hundred.(so call Samples)
inside should have no sensitive data and one more sheet with desired result (put it manually) with some Description
it value than thousand words.
step1, Go advance,
step2, manage attachment
step3, choose file or drag file,
step4, click "upload" in the right
step5, go down and click "close the window"
Step6, post at normal.
KeyCode1 >I is the constant.
KeyCode2 >N is what I want to match/align to KeyCode1
There are 35000 rows
If I not understand wrongly,
In book2, (your attachment)
you want to check whether the column K (key code 2) have incurred in column A,b and c (key code 1)
if solely check for occurrence,
try
=COUNTIF($A$2:$C$273,K2)
the value 0 mean not such code in column A,B and C
if want to return result,
you may create three column,
in P2
=INDEX($F$2:$F$273,MATCH(K2,$A$2:$A$273,0))
It show result in of N row in column F based on number of row that K2 incurred in A2:A273
in Q2,
=INDEX($F$2:$F$273,MATCH(K2,$B$2:$B$273,0))
It show result in of N row in column F based on number of row that K2 incurred in B2:B273
R2,
=INDEX($F$2:$F$273,MATCH(K2,$C$2:$C$273,0))
It show result in of N row in column F based on number of row that K2 incurred in c2:C273
Thank you for your help and response.
I am trying to match columns K,L,M,N with column A
Column A and K have matching data.
It would be helpful if you could include a sheet showing what the data should look like after the formulas are applied so that we could get an idea of what you want.
That said I am going to make a guess that the following formula produces the results you are looking for on sheet 2 of the attached file:Formula:Please Login or Register to view this content.
If there are 3500 rows of key code 1's then modify the formula to read: =IFERROR(INDEX(Sheet1!K$2:K$3500,MATCH($A2,Sheet1!$K$2:$K$3500,0)),"")
Once the formula has been pasted into cell K2 on sheet 2, the fill handle may be dragged across to N2 and while K2:N2 are selected the fill handle for N2 may be dragged down as far as needed.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Thank you this worked.
I really appreciate your efforts
You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks