How to map two columns

Question

I have Five columns.

E.g.

1. Column1: Name
2. Column2: surname
3. Column3: mapping
4. Column4: Mapped data

Columns contain data like

      Name         Surname        Mapping    Name1   Surname1
  1   ABC          1  AAAA          3         ABC      QQQQ
  2   XYZ          2  XXXX          1         XYZ      AAAA
  3   OPQ          3  QQQQ          4         OPQ      QQQQ
  4   RST          4  RRRR          2         RST      RRRR

Now my aim is to map name column to surname by using mapping column and result should be stored at Name1 and Surname1 column. I have more data in Name and Surname column, by writing number in Mapping column it will automatically map the surname to Name (the choice is given to user for entering number in mapped column then map the data accordingly) and result should be copied in Name1 and Surname1.

I am not getting any idea to achieve this using VBA. coding Plz help me.....

Answer 1

You don't need Vlookup, just a concatenate:

If you want to have the numbers in front of your surname, use the second option.

The formulas for copy&paste:

=CONCATENATE(B2;" ";OFFSET($C$1;D2;0))

=CONCATENATE(B8;" ";RIGHT(OFFSET($C$7;D8;0);LEN(C8)-FIND(" ";C8;1)))

Answer 2

Have you tried using Vlookups to map? Vlookup(Lookup_Value,Table_array,col_index_number,[range_lookup]) Vlookup(firstname, the last name column you are referencing, the column number of the first name column, false) -false is for exact matches -I've also pasted a helpful link below. Goodluck!

https://support.office.com/en-us/article/VLOOKUP-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1?ui=en-US&rs=en-US&ad=US&fromAR=1

Answer 3

Sorry I didn't realize you wanted to have them together in the same column like what you have above. The concatenate function works:
http://www.gcflearnfree.org/excelformulas/using-concatenate-to-combine-names/1/

or if you go to excel you can go to the data tab, and click on text to columns.

1. Click on the data tab
2. Click on Text to columns
3. Check Delimited
4. Choose the delimiter that separates your data
5. Modify your column data format (if it's general, number, etc.) in this case it would be general.
6. Click finish

and you're done!

Answer 4

Put this formula into the mapped data row and it should do what you want

Formula MappedData()
Dim CurrentCell As Range
CurrentCell = Application.Caller.Address

MappedData = Cells(CurrentCell.Row,1).Value & " " & Cells(Cells(CurrentCell.Row,3),2).Value

End Formula