Lookup and add in Excel

December 7, 2010 at 07:57:52
Specs: Windows XP
I have a macro that is really not working correctly. It's pretty long so I can't post it here, but what I am trying to do is look at 2 worksheets for specific words, 10 of them to be exact. If they are found I need to sum up 2 cells associated with each word. For example if it found "jump" 4 times it would need to sum the values in columns '"b" and "c" associated with the row that the 4 "jumps" were found. I can kind of do it, but it is so inefficient.

zip 32 $80
zip 6 $67
jump 140 $109
jump 60 $88
jump 108 $55
jump 20 $92
frank 24 $90
frank 24 $50
aero 30 $50

There would be values on 2 worksheets.


See More: Lookup and add in Excel

Report •


#1
December 7, 2010 at 14:07:55
I'm not sure what you are asking us to do.

Are you asking us to start from scratch and write a macro to do what you wrote about in your OP? If so we are going to need more information related to the layout of your worksheets and the "list" of words you are searching for.

Are you asking us to fix the macro that you said you can't post? (BTW Why can't you post it?) Fixing something we can't see would be difficult.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#2
December 7, 2010 at 21:08:15
no. I did not word this correctly. I will keep my macro for now, even though it's long.

Report •

#3
December 8, 2010 at 05:09:00
No?

How is "No" an answer to my questions?

I asked 3 questions:

Are you asking us to start from scratch?
Are you asking us to fix your existing macro?
Why can't you post your code?

How does a single "No" address all three questions?

I still don't know what you are asking us to do for you.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

Related Solutions

#4
December 8, 2010 at 07:53:03
I meant no to I don't want you to start from scratch. But I can't post the code because it is very large, to me, and it has sensitive information. I'm just trying to figure out what to do, in general, so I can write the code. Maybe just the concept. I won't learn if someone else writes it for me. I'll post back in a while with some form of the code that does not have the sensitive info.

Report •

#5
December 8, 2010 at 08:09:27
I'm glad you are willing to try this on your own.

First, I'm not quite sure why you don't just use SUMIF to get your totals, but if you want to use a macro, feel free.

If all you want is the concept, it sounds like this is what I might try:

1 - Put the 10 words you need to search for in a list, say A1:A10
2 - Use a For-Next loop to loop through A1:A10 and Find each word in the ranges with the actual data. As each piece of data is found, add the desired cells associated with the data, placing the updated total in a variable. Once all of the instances of that word have been found, the variable would contain the total amount and could be put in a cell or used elsewhere in the code.

Look up the Find Method in VBA help. It shows a great example of using Find to search a range, including how to Find multiple occurrences of the same text, and then how to perform operations on what it found.

Once you've tried something and need more assisatnce, post what you've tried and we'll see if we can offer some tweaks.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#6
December 8, 2010 at 10:34:35
Thank you. I am going to try either the sumif and/or the for next with a find.

Report •

#7
December 8, 2010 at 10:38:56
Can I use the sumif if the data is contained on 2 separate worksheets?

Report •

#8
December 8, 2010 at 11:00:18
Sure, just use it twice, once for each sheet, and sum the results.

=SUMIF(Sheet2!A1:A4,"Jump",Sheet2!B1:B4) +
SUMIF(Sheet3!A1:A4,"Jump",Sheet3!B1:B4)

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#9
December 8, 2010 at 11:01:49
WOW... That was easy. Here's what I did

=SUMIF(Monday!C$4:C$1000,$H27,Monday!P$4:P$1000)+SUMIF('Tues - Sun'!C$4:C$1000,$H27,'Tues - Sun'!P$4:P$1000)

Works Perfect!!!

Thanks


Report •

#10
December 8, 2010 at 11:08:50
Anytime.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#11
December 9, 2010 at 11:14:42
Please read the the following line and click on the words "How To"

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#12
December 9, 2010 at 11:59:27
I'll try this again...

Hit a little snag. I have to multiply values from two rows.

From above I would sum values in cells in column P associated with the find in column C.

In addition to this I perform the same function for column Q associated with column C. So basically I have 2 cells that have sumif's. One is for P and one is for Q. I was multiplying these two togetther, but that won't work. I'll show you an example of what is wrong. Total 1 and total 2 are correct. They use the sumif function. Right now total 3 multiplies total 1 and total 2. I need for total 3 to be the value in total 4.

col C	col P	col Q			
					
a	1	10			
a	2	15			
b	1	2			
b	2	25			
d	1	10			
d	2	10			
					
result	total 1	total 2	total 3	total 4	formula
a	3	25	75	40	1*10+2*15
b	3	27	81	27	1*2+2*25
d	3	20	60	30	1*10+2*10

There are varing occurances, maybe 1 to 100, of each value in column C. this example shows 2 of each.

Can I do this without a macro?


Report •

#13
December 9, 2010 at 12:25:07
1*2 + 2*25 = 27? In whose world?

Assuming your data is in C1:C6, P1:P6 & Q1:Q6, these will return 40 for a, 52 for b, 30 for d.

Adjust as required.

=SUMPRODUCT(($C$1:$C$6="a")*($P$1:$P$6)*($Q$1:$Q$6))

=SUMPRODUCT(($C$1:$C$6="b")*($P$1:$P$6)*($Q$1:$Q$6))

=SUMPRODUCT(($C$1:$C$6="d")*($P$1:$P$6)*($Q$1:$Q$6))

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •


Ask Question