• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please Help. Need to Replace Words with Numbers from the Other Sheet

Louie Zabala

New Member
Hello guys. Good day. I needed some help.
I have two spreadsheets source (sheet 1:nodes) and destination (sheet 2:edges).

I need to have the entries in sheet 2 be replaced with numbers based on their order in sheet 1.

Ex.
This is what the first three items in colums A and B in sheet 2
TYPE OF USERTITLE
APSPA neglected dimension in service-learning pedagogy : developing projects from the perspective of rural community stakeholders
ASFAn ambivalent state : the crossover of corruption and violence in the Philippines.
ASFAssessment of the DLSU undergraduate mechanical engineering on-the-job training program

I want them to appear like

TYPE OF USER TITLE

1 7
2 8
2 9

Please help. Attached are the files. Thank you.
 

Attachments

  • Nodes.xlsx
    23.1 KB · Views: 5
  • Edges.xlsx
    22.3 KB · Views: 5
Have both workbooks open.
In Edges.xlsx, Sheet1, cell C2, this formula:
Code:
=INDEX([Nodes.xlsx]Sheet1!$A$2:$A$277,MATCH(A2,[Nodes.xlsx]Sheet1!$B$2:$B$277,0))
Copy across 1 cell to the right, then copy both down as far as necessary.
Select all the formulae cells, then copy, paste-values in situ, to replace the formulae with their values. Then delete columns A and B.
Add headers.
 
Back
Top