Need to merge data from two worksheets automatically

[zachzach]

Hey all,

First off, I would appreciate it if I could be directed to the right forum and place to ask my question:

I would like to combine two data from two worksheets. Because I have so much data I need excel to:
1)find the text that matches from column A of both worksheets (1 & 2)
2)import columns B through E from the corresponding matched text from worksheet 2 into columns F through I in worksheet 1.

In the attachment the goal of this representative sample is to place from worksheet 2
76076 19900216 1984 .
into columns F through I in worksheet 1, so that the completed column looks like this
Cisco Systems 19900216 3577 No 18.000 76076 19900216 1984 .



Thanks way in advance for you help and I will try my best to follow instructions as they are politely presented,

Zach

[:) Sixthsense :)]

In F2 cell of Sheet 1

Please Login or Register  to view this content.

Drag it down and right.

[FDibbins]

Hi Zachzach and welcome to the forum - the forum you selected is just fine

based on the data you supplied, you can use this formula, copied down. copy it across, but change the 3 (bolded) to match the column you want to use. If you had headings in each of the tables, you could have used index/match/match, and copied it down and across without the need for manually adjusting the column number

=IFERROR(VLOOKUP($A2,'2'!$A$1:$I$8,B,FALSE),"")

[huuthang_bd]

Hi zachzach,
You can try this solution:

Put this formula into cell F2 of sheet 1 then fill down and fill right

Please Login or Register  to view this content.

Regards
huuthang

[zachzach]

Sixth Sense
"In F2 cell of Sheet 1

Formula:
=IFERROR(VLOOKUP($A2,'2'!$A:$D,COLUMN(B$1),FALSE),"")
Drag it down and right."

This works but now I need to use this formula on my actual file and not the sample. What values do I need to change or update?

FDibbins
When I try your formula I am prompted to open a file. How should I proceed?

[FDibbins]

impossible to say without seeing what data/layout you are working with. the vlookup syntax is...
=VLOOKUP(what-you-want-to-find,range-to-search-in,column-that-contains-the-match,FALSE) FALSE is for finding an exact match

[zachzach]

FDibbins et al,
Because this is publically available info, and the quick response and professionalism, I've attached the workbooks that I'm trying to merge.

Thanks again for your help.
https://www.dropbox.com/s/t1qpme2zul...kbook%201.xlsm

[zachzach]

I need to be able to automatically search for the name and then input the three fields Perm, Founding Date, and Rollup Dummy. The sheet entitled jaypost(sorry forgot to change it) is the one that I want to keep.

[FDibbins]

try this, copied down and across...
=INDEX(Sheet1!Copy_of_age7511020612,MATCH(jaypost!$A2,Sheet1!$C$1:$C$9397,0),MATCH(jaypost!F$1,Sheet1!$A$1:$E$1,0))

edit: for future reference, please upload your files on the forum, not on another web-site. not every-one can access websites like drop-box (for instance, i can access it at home - now, but not from work)

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[:) Sixthsense :)]

In Jaypost Sheet

In F2 cell

Please Login or Register  to view this content.

In G2 cell

Please Login or Register  to view this content.

In H2 cell

Please Login or Register  to view this content.

The above formula's are same, just change in output Column #

[zachzach]

FDibbins
Is there any way to remove the N/A error? or make it so that I don't see it?

SixthSense
Still trying yours...hold on.

You guys are pro.

[zachzach]

FDibbins
Thanks for the heads up about the attachment.

Sixth Sense
I suppose I have the same question. Is there a way for me to hide a small script symbol that I get?

Other than that both of these formulas work perfectly. I will definitely be promoting this forum as much as possible and also if there is a way to rate each of you let me know. Thanks

[:) Sixthsense :)]

It's better you should stick with FDibbins Formula

To Mask Error of the FDibbin Formula
=IFERROR(FDibbinsFormula,"")

[:) Sixthsense :)]

Is there a way for me to hide a small script symbol that I get?

Don’t know what do you mean by this word small script symbol

[zachzach]

It was a small picture that appeared in each cell. It might have been related to some excel notification setting that's turned on. I don't really know. That's ok though. I am going with FDibbins formula. Thank you both very very much.

[:) Sixthsense :)]

It might have been related to some excel notification setting that's turned on

If possible please attach a screenshot to fix it...

[FDibbins]

Thanks for the back-up Six

zach, I have seen those little "icon" things before too. I think they get pulled across sometimes if you copy/paste things from here - but only on a very few occasions. I dont know what triggers it, and I just tried to copy both Six's and my formulas, and did get it this time. I just delete it when I get it.

Happy to help and thanks for the rep and kind words - always appreciated