Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Create if partial match then copy and paste data from one cell to another

Hello,



Hopefully, I can explain this well which is probably half the challenge. What I am trying to accomplish is a bit complicated.
For the purpose of simplicity I will
ask how I can achieve X without going into too much detail, then I will explain Y which is exactly what I am wanting to accomplish. Finally Z the complications of what I am trying to do



X / How can I create and if statement that says IF cell A2 (sheet2) finds a partial match in range A:A (sheet1) then copy data from cell B2(sheet2)and paste into cell B2 (sheet 1).




Y/ I have a WB with multiple sheets. I have a Feed sheet that automatically pulls data from the web. I have a Scores sheet that currently has all head to head matchups each week for an entire season. So I want to check the a cell on the Feed sheet to find a partial match on the scores sheet and if partial match exists then copy a particular cell that contains the score (from the feed sheet) and paste it to the score sheet (in the cell next to the corresponding team)
In Plain English (hopefully)...


I need to look at Cell A3 (on the Feed sheet) which has "New York Jets" then search for a partial/similar match on the score Sheet (which is NY JETS, in this case). NY JETS could be in column B (rangeB2:B257) OR column D (rangeD2:D257) BUT I need to search by column not row then once it is found check the cell to the immediate right and only if the cell is blank copy data in H3 (!feed) to (!scores) to the right cell...

This is what happens if working right =

look at cell A3 (!feed) sheet = "New York Jets" then search (!scores) sheet Column B and Column D by row, finds "NY JET" as match in cell D8, if cell E8 is blank then copies cell H3 (!feed) sheet, and paste to cell E8 (!scores), if cell is not blank continues search until blank cell to the right of matching cell is found. Once this is done it moves on to the next row.
look at cell A4 (!feed)= "New England Patriots" then search (!scores) sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to cell C8 (!scores)




Z/ A complication is that team names are repeated over and over on the score sheet (the feed sheet is overwritten each week) so the search needs to check cells by row (not by column) to check cell B2 then cell D2 then move on to B3 then D3 then B4 and D4 and so on to find the first match then check to see if the cell to the right is blank. Once the match is made and the score is copied and pasted I need to move to the next cell on the feed sheet to start the search process over again checking Cell A3 to A74, and if a match is not found move on to the next cell and search. For example Cell A3 + A4 have team names matches should be found but cell A5 is blank A6 say Game Final and A7 is blank
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default Create if partial match then copy and paste data from one cell to another

theaxemonkey wrote:

Hopefully, I can explain this well which is probably half the
challenge. What I am trying to accomplish is a bit complicated.
For the purpose of simplicity


This is "simplicity"?

[snip]
This is what happens if working right
look at cell A3 (!feed) sheet = "New York Jets" then search (!scores)
sheet Column B and Column D by row, finds "NY JET" as match in cell D8,
if cell E8 is blank then copies cell H3 (!feed) sheet, and paste to cell
E8 (!scores), if cell is not blank continues search until blank cell to
the right of matching cell is found. Once this is done it moves on to
the next row.
look at cell A4 (!feed)= "New England Patriots" then search (!scores)
sheet Column B and Column D by row, finds "NEW ENGLAND" as match in cell
B8, if cell C8 is blank then copies cell H4 (!feed) sheet, and paste to
cell C8 (!scores)


The only suggestion I have at the moment is to build a Collection:

Dim Teams As Collection
Set Teams = New Collection
' scores sheet feed sheet
Teams.Add "NY JETS", "New York Jets"
Teams.Add "NEW ENGLAND", "New England Patriots"
'etc.

Then, when your code finds a match from the feed, it can get the info it
needs to find on the scores sheet like this:

infoToFind$ = Teams.Item("New York Jets")

This probably won't magically solve all your problems, but it *might* help.

--
It's a small world, but I wouldn't want to have to paint it.
-- Steven Wright
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to copy and paste a partial row (i.e. not the EntireRow) CROD Excel Programming 5 November 14th 08 02:11 PM
1 Create a macro to Copy & paste certain data to another sheet Lin1981 Excel Discussion (Misc queries) 1 November 7th 08 12:56 AM
Find partial match from column A,B and fill partial match in C? Tacrier Excel Discussion (Misc queries) 4 October 24th 08 11:24 PM
Copy Cell, Paste based on a match. Pete Excel Programming 2 July 23rd 07 07:20 PM
create a macro to copy/paste cell data joshua.v Excel Programming 1 September 14th 04 04:42 PM


All times are GMT +1. The time now is 04:26 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"