Automatically search and copy data from one sheet to another based on partial match

**QBF** · 07-09-2013, 12:19 AM

Hi all.

I have an Excel Book with two Sheets - Sheet1 and Sheet2 and use them to aggregate news stories from Government agencies.

Sheet2 has three columns - Domain(A), Portfolio(B) and Agency(C)
This sheet is populated with the domain name that identifies a particular agency, the government portfolio that it belongs to and the name of the agency

eg. health.gov, Health, Department of Health

Sheet1 has three columns - Portfolio(A), Agency(B) and Link(C)

I copy and paste URLs into C and as I do that I want excel to automatically search the Domain column(A) in Sheet2 for a partial match from the link that I insert and enter the corresponding Portfolio (A) and Agency (B) from Sheet2 into those fields in Sheet1.

eg. In Column C in Sheet1, I paste something like http://www.health.gov/random_folder/random_file.html and I want Excel to automatically search Column A in Sheet2 and if it finds a match based on any part of the URL, to automatically insert "Health" in Portfolio (B) and "Department of Health" in Agency(C)

Any suggestions to automate this?

Thanks in advance.

**FDibbins** · 07-09-2013, 12:44 AM

Hi and welcome to the forum

We can probably this with mid() and index/match, but I will need to see a few more URL samples, as well as what your expected outcome with them would be.
(for instance, if you have http://www.health.gov/random_folder/random_file.html in A1...
=MID(A1,SEARCH(".",A1,1)+1,SEARCH("/",A1,SEARCH("//",A1,1)+2)-SEARCH(".",A1,1)-1)
will give give you health.gov

Do you have a sample workbook that you can upload? (no sensitive info)
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.

**QBF** · 07-10-2013, 11:13 AM

Hi FDibbins - and thank you for the welcome and very kind offer of help.

I've attached a book that contains two sheet. Sheet 1 is an example of the sheet that I paste news links into column C. At the moment, I then flick over to Sheet 2 and copy the appropriate Portfolio and Agency cells then paste them back next to the link. As you can imagine, that's quite laborious when there's a lot of links.

What I would love to find is a formula or macro that automatically searches Sheet 2, Column A (Domain) for a match whenever I paste a link in Sheet 1, Column C.

The examples show a good sample of links.

Please let me know if I can provide any more useful info

Thanks again!!

excelforum_demobook1.xlsx

**FDibbins** · 07-10-2013, 01:39 PM

Thanks for the file

Give this a shot. Add a helper column in sheet1 (I used D) and copy this down...
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2,"http://",""),"www.",""),".au","")=VLOOKUP(LEFT($D2,SEARCH("/",$D2,1)-1),Sheet2!$A$2:$C$16,2,0)
This will remove all the http and www and au stuff

Then in sheet1 A2, copied down, use this...
=VLOOKUP(LEFT($D2,SEARCH("/",$D2,1)-1),[excelforum_demobook1.xlsx]Sheet2!$A$2:$C$16,2,0)
for any additional columns, change the ,2,0) at the end to ,3,0) etc

Let me know how you make out?

Automatically search and copy data from one sheet to another based on partial match

LinkBack

Thread Tools

Rate This Thread

Display

Automatically search and copy data from one sheet to another based on partial match

Re: Automatically search and copy data from one sheet to another based on partial match

Re: Automatically search and copy data from one sheet to another based on partial match

Re: Automatically search and copy data from one sheet to another based on partial match

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions