Pull in data based on certain values
Hi,
I have a major data project to complete and I remember there being a short cut to the finish line. I just can't seem to remember how. In the attached document you will find a sample spreadsheet with the full question being on page 3. If anyone could help me, I would truly appreciate it. It's very difficult and maybe cannot even be done, but I am just hoping for the best. Thank you!
Last edited by FDibbins; 05-29-2014 at 05:14 PM.
Hi and welcome to the forum
Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)
To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
Edit: I have your answer ready and waiting
Last edited by FDibbins; 05-29-2014 at 04:05 PM.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
@ joemand: pls do what FDibbins ask You to do, because I want ot see his solution (and want to compare)...
Regards
Miroslav R.
(If You like my solutions, feel free to add reputation.)
OP is off-line now, and I may lose/forget what I worked out lol So I have changed the title. Try this, copied down...
=IFERROR(INDEX(Sheet2!$B$2:$B$25,MATCH(Sheet1!$A2,Sheet2!$A$2:$A$25,0)+1),"No Match")
hm... looks good but the last line is not correct I think (see item '1015KRRB3-LAME').
Is it possible to manage this kind of situation via regular (not in VBA created) formulas?
hmm actually thats not the only 1 that is not correct. I based that on all "red" parts on sheet 2 having 2 rows each. Where there is only 1 row for the part, the formula fails. I think I can makwe a work-around though. (that was a regular formula, not VBA creates, btw)
OK try this...
=IFERROR(INDEX(Sheet2!$B$2:$B$25,MATCH(Sheet1!$A2,Sheet2!$A$2:$A$25,0)+IF(INDEX(Sheet2!$A$2:$A$25,MATCH(Sheet1!$A2,Sheet2!$A$2:$A$25,0)+1)="",1,0)),"No Match")
nice & working...
(there is always something to learn)
@ M.R. Thanks for the catch on that, and thanks for the rep
Hi FDibbins, I must first say that I am dazzled by your work. With that being said, I also must thank you deeply. I was going to have to have to hand type this data into nearly 1,100 different rows.Originally Posted by FDibbins
@ M.R. Thanks for the catch on that, and thanks for the rep
However, I do have a few questions. If you look at Sheet 2, you may notice that there are pairs of data separated by borders. For example, row 2-3 or 4-5 or 6-7, etc. Then you may notice that some of these pairs for Red Company are completed filled (i.e. row 12-13) while most others are not (i.e. 2-3). This is because they sometimes only offer the PRM and not the LAME, but sometimes offer both. Blue company typically only offers Bad, and rarely Nice and Bad.
Typically you will have rows such as 1-2 where Red lists a PRM and in column Blue it offers a Bad underneath in row 2. Although, there are certain situations like rows 12-13 where Red Company list the LAME part and then the PRM part underneath. But in the column next to it Bad is labeled in Row 12, rather than under PRM. Which is why in sheet 1 it is displaying a N/A in Blue rather than "09067".
Does this make sense or am I just twisting this? I'm awfully sorry and I would again appreciate the help. Nevertheless, I love what you've already helped me with. Thank you.
Thanks for the kind words, always appreciated
OK I kinda get what you are saying, but Im not sure what you are askingPerhaps, if you provided a few sample answers (entered manually if needed)?
Haha, understandable. In the attached document you will find that I manually edited the Excel Sheet and color coded it to correspond with page 2. It compares your formula with what had to be manually typed in. Basically, not all the data is being pulled correctly.
OK try this monster in B4...
=IFERROR(IF(LEFT(INDEX(Sheet2!$A$2:$A$25,MATCH(Sheet1!$A4,Sheet2!$A$2:$A$25,0)),5)=INDEX(Sheet2!$A$2:$A$25,MATCH(Sheet1!$A4,Sheet2!$A$2:$A$25,0)-1),INDEX(Sheet2!$A$2:$A$25,MATCH(Sheet1!$A4,Sheet2!$A$2:$A$25,0)-1),
INDEX(Sheet2!$B$2:$B$25,MATCH(Sheet1!$A4,Sheet2!$A$2:$A$25,0)+IF(
INDEX(Sheet2!$A$2:$A$25,MATCH(Sheet1!$A4,Sheet2!$A$2:$A$25,0)+1)="",1,0))),"No Match")
Haha, yes thank you. Showed the final result during a conference meeting and all of the bosses were shocked that I had solved it so soon. You my friend have a strong talent. Thank's again!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks