Vlookup Add Multiple instances of same string

**jalweber** · 04-12-2011, 04:17 PM

Hi All,

New to the forums and not all that great with Excel...I'm uploading a sample project that I will reference here...

What I need to do is have a specific string tested for and find ALL instances of it. After I have found all instances I need to take a value in the same row from each instance and add them all together...

Does anyone know if I can do this with vlookup or is it going to become a more intense problem to fix?

In my example sheet I have a sheet named STEEL that is the formulating sheet. Currently I am testing it on just the 10x20 B sheet but would like to combine it to include 10x20 A&C as well as Gangway, etc... You will see a couple of lines started but I can't figure how to get it to return multiple values or even if that is possible

Any help is greatly appreciated!

Thanks,
Justin

**NBVC** · 04-12-2011, 04:39 PM

If you list your sheetnames in a separate area, like J2:J8, then enter a formula in B1 like:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$J$2:$J$8&"'!D1:D1000"),"*"&TRIM(SUBSTITUTE(A1,TRIM(LEFT(RIGHT(SUBSTITUTE(A1," ", REPT(" ",100)),100),100)),""))&"*",INDIRECT("'"&$J$2:$J$8&"'!H1:H1000")))

and copy down.

This automatically takes out the last word (like "Angle" from each of the cells in column A and looks for a match)...

If you wanted the word to be part of the match (you'll get alot of 0's) then:

=SUMPRODUCT(SUMIF(INDIRECT("'"&$J$2:$J$8&"'!D1:D1000"),"*"&A1&"*",INDIRECT("'"&$J$2:$J$8&"'!H1:H1000")))

**jalweber** · 04-13-2011, 07:23 AM

NBVC,

I think this solves my problems, with a little tweaking I realized that I need to calculate the lengths indiviually on each sheet because they vary for each part and then have your formula search and return the sum of the lengths.

THANK YOU VERY MUCH! If you don't mind me asking, what exactly is that line of code doing? I'm nosy and like to know whats going on in my code...

Thanks again!

**jalweber** · 04-13-2011, 07:50 AM

If you would be so kind, I do have another question/problem now that I have that figured out... I have attached my updated file.

On the TakeOFF sheet I would like it to populate itself with the description and total length of only items greater than 0.00 from sheet 'Steel'. I'm kind of a newbie when it comes to programming with Excel, so again any help would be greatly appreciated!

Thanks in Advance!

**NBVC** · 04-13-2011, 08:13 AM

Do you mean as attached?

In TakeOff sheet, A4:

=IFERROR(INDEX(STEEL!$B$2:$B$151,SMALL(IF(STEEL!$B$2:$B$151>0,ROW(STEEL!$B$2:$B$151)-ROW(STEEL!$B$2)+1),ROWS($A$1:$A1))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER (this is a special ARRAY Formula).

In B4:

=IFERROR(INDEX(STEEL!$A$2:$A$151,SMALL(IF(STEEL!$B$2:$B$151>0,ROW(STEEL!$B$2:$B$151)-ROW(STEEL!$B$2)+1),ROWS($A$1:$A1))),"")

confirmed with CTRL+SHIFT+ENTER not just ENTER (this is a special ARRAY Formula).

**jalweber** · 04-13-2011, 08:18 AM

Wow, thank you very much for the quick responses! You have made my life 100 times easier today.
Hopefully someday I can provide help to someone as well.
I'm marking this as solved, thanks so much!