Thread: XL Colums Map from Doc 1 to Doc 2

1. XL Colums Map from Doc 1 to Doc 2

Register for a FREE account, and/
or Log in to avoid these ads!

I have two columns Col A and Col B in one xl document. Both Col A and Col B contains numbers under each cell and they are mapped each other (Col A is mapped to Col B). I have same Col A in second document (Col A in document 1 and 2 are the same) and need to map doc 2 Col A with doc 1 Col B. I want to map cell to cell so that doc 1 Col A and Col B should exactly map with doc 2 Col A and Col B.
So please send me if any Macros for the above.

Copy doesn’t work since Doc 2 Col A number order is different from Doc 1 Col A ordering.

Example:

Doc 1 Col A Col B
01 100
02 101
03 102
04 103

Doc 2 ColA Col B
03 ??
01 ??
04 ??
02 ??

Thanks for the reply and solution in advance....

2. Hello Kotigari,

You can use VLOOKUP formula.

you can type the following formula in Col B (Cell B4 or whichever is your starting cell) of Doc 2

=VLOOKUP(A4,'[Doc 1.xlsx]Sheet1'!\$A:\$B,2,0)

in above formula A4 is my files starting point i.e (03 in Doc 2 is located at cell A4)

once you enter the formula drag till end.

Regards,
Hiten Patel

3. Doc 1.xlsx
Doc 2.xlsx

Thank you Hiten,

The formula is working but not exactly to me. The problem is I have more than one entry in cells.
This time I have attached exact example files. Hope I am clear this time.

Thanks in advance....

4. Originally Posted by kotigari
The problem is I have more than one entry in cells.
You can not mix apples and oranges for plum jam,
First: remove multiple line from the cell data (Find / Replace)
Second: separate data from cells which contain more entry data in cell
Third: use a formula that the colleague gave
btw: use additional columns

5. I maid small change in attachment "Doc 2_" to improve readability.
So please use example docs (Doc 1 and Doc 2_).

Thanks,
Shiva Kotigari

6. Hi navic,

Thank you. I have separated data (Line) and numbers and uploaded the new Doc 2_.xls file. Doc 1 and Doc 2_ will give a better understanding about my problem.

If I separate multiple entries into one (as you advised) and use the formula, I am sure it works. But again I need to combine them and my document contains multiple entries in lot of cells. So its all a big and manual job. So I am trying to automate this process without much efforts.

Thanks,
K...

7. Shiva,

Can you please send Doc 1 , I want to see what data you have in column B, in this message you have only attached Doc2.

8. Hi,

I have attached both docs Doc 1 and Doc 2_. Hope it helps.

Thank you for your time.

9. I am using these forms first time. So don't know what is mean by cross post.
I thought both excelguru and Excel Forums are different.

But thank you for your advice.

Page 1 of 2 1 2 Last

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•