How to Search/Find Text and add values in corresponding columns

[Excelmanineedhelp]

Hi everyone i need help creating a marco that will first search throught the worksheet and find like "ACT" and then will search under "ACT" for the "Data" and then for every "data" it will add the vaules in column F and divide by 64 and put answer on a differnt work sheet. A EX.

A ----------------F
Act
Data---------------1
Data---------------2
Data---------------3
Act
Data---------------7
Data---------------2

and will keep looping this till the last "Act" was found and "data" where add and divided.

Thank you,

Excelmanineedhelp

[zbor]

Where is that data stored? That need to be divided?

Also, uploading example workbook would be easier to see how your output should look like.

[Excelmanineedhelp]

Sorry this is my first time making a marco so I am not sure how to answer first question. What needs to be divided are the sum of the Data, not sure if this is clear, so like in my first post under the first "PGN" the three "data" values in column F will be add so 1+2+3 and that answer will be divided by 64 and the next "Act" the two "data" values under it will be add so 7+2 and answer divided by 64 . So the marco should loop so from "PGN" to "PGN" and the inbetween "data" values in column F should be added and divided by 64. Not sure if this cleared anything up.


Here is the example workbook

[tigeravatar]

Excelmanineedhelp,

Welcome to the forum!

I do have a couple questions:
After summing the data group and dividing by 64, is that considered a single output (so that each data group of ACT will have its own output), or will they all be summed or averaged together?

For example, if there are 2 ACT's found on the sheet.
The first ACT has 4 DATA with corresponding values: 1, 2, 3, 4
Sum then divide by 64 = 0.15625

The second ACT has 3 DATA with corresponding values: 5, 6, 7
Sum then divide by 64 = 0.28125

Are those two separate outputs? Or should there be only 1 output where both of them are summed (0.4375) or averaged (0.21875) or a different operation to provide a single output?


Next question:
In your posted workbook, there are no cells that contain the word "ACT". Instead, there are cells that contain the word "PGN" with DATA values. Should the macro look for "PGN" instead of "ACT"?

[Excelmanineedhelp]

To answer your first question they are two separate outputs, and yes i am sorry the marco should look for "PGN"

[tigeravatar]

Excelmanineedhelp,

This macro should accomplish what you're looking for. On your test workbook, it completed in 0.046875 seconds:

Please Login or Register  to view this content.

[Excelmanineedhelp]

Thank you so much it worked like a charm. You do not know how much you helped me out. Thank you once again for everything.

[tigeravatar]

You're very welcome, and thank you for the feedback

[Excelmanineedhelp]

Tigeravatar thank you once again but i have do another question
Hi Everyone,

So the marco that tigeravatar helped me make prints data on a new sheet but i want the data to be printed on the same sheet. But the trick here is that i want to marco to printed the stored array in the next blank column and will print the data on the same cells row where the word PGN is located.
EX.

Column A---------(next blank column)
PGN-------------(stored array printed here)
Data
Data
PGN--------------(next stored array)

Thank you again,

Excelmanineedhelp

[tigeravatar]

Excelmanineedhelp,

Are there blank columns between A and F? or should the output start at a column beyond F?

[Excelmanineedhelp]

The output should start at a column beyond F, but it will not be the same for every worksheet so like some worksheets may have column G blank and others may have column S and it varys from worksheet to worksheet.

[tigeravatar]

Excelmanineedhelp,

Something like this should work for you then:

Please Login or Register  to view this content.

[Excelmanineedhelp]

Once again Tigeravatar you do it again. Its work!! Thank you so much for helping me again. You are God sent.

[tigeravatar]

Private Message I received from Excelmanineedhellp:

Hello again TigerAvatar,

I want to thank you for all you help last time. You really helped me alot. So the what I need help with now is a little more complicated and i am not sure how to explain what needs to be done but i will try my best, and if you have any question just let me know.

So every thing work on the last marco perfectly, what i need now is for the data that is stored into the array( from the last macro,which i converted to percent) to still be printed in the blank column but now i need that data to be multiplied by the data that is in column J and the total of all the previous data and data in column J to be add and printed on the next row at the bottom. I also need the data in column J to be add up and place at the next blank row at the end of that cloumn(see additional information). Lasty i need the total of column J and the product of the data of column J and the array being multiplied to divided and stored on the same row as the total of column J and the product of data of column J and the arrays data being multiplied.

Here is some additional information:
Every N/A value in column J are equal to 1
Before the values in column J can be multiplied by the percents it needs to be divided by 1000 and stored into array1 so like: 1000/ (value of column J) so N/A = 1 Ex: 1000/1 = 1000
After you get that value it will be multiplied by the percent and also stored in array2 so: Ex. (1000/1) * 62.5
And then all the values stored in array1 will be added together and printed at the end of column J
And all the values stored in array2 will be added and printed on the same row as the total value of array1
Lastly i need the value if array2 to be divided by array 1 and printed on the same row as array1 total was printed and array2

So i know that is alot so i will put down a example:

A---------J--------
PGN-------N/A-------.625-----62.5=( 1000 * 62.5) = 62500 (not printed)
Data
Data
PGN-------20----------1------100= ( 50 * 100)= 5000(not printed)
Data
Data
Total----1050----------------67500---- (67500/1050)= 64.2857 (printed)

Heres code

Please Login or Register  to view this content.

[tigeravatar]

Excelmanineedhelp,

From the forum rules (link in my sig):

4. Don't Private Message or email questions to moderators or other members. The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.


It's not that you did anything wrong really, you're just looking for additional help and I get that. However, others might also benefit from the questions and answers provided on this forum, so we try to keep all questions/responses publicly visible.

[tigeravatar]

Alright, now that the ugly stuff is out of the way, I see you posted a new example workbook in post #13. Taking a look at it, and will get back to you when I have something to offer

[Excelmanineedhelp]

Tigeravatar,

Sorry about that I will go a review rules so this will not happen again. Thank you also for the quick response.

[tigeravatar]

Alright, I've looked at your example file, and been over your explanation, but I don't understand what it is you need. There is conflicting information, and results that aren't explained.

I will use two sections from the workbook you posted.

Column A	Column F	Column J	Macro Result
PGN	0	N/A	0.625
DATA	8
DATA	8
DATA	24

In this example, the macro adds up 8+8+24 then divides the result by 64, so 40/64 = 0.625
Column J is N/A, so 1000/1 = 1000. 1000*0.625 = 625
So should the result there be 625? In your example file, it seems like the result should be 62.5, and in your earlier explanation, you stated the result would be 62,500 and I don't know where either of those numbers came from given your explanation.

Column A	Column F	Column J	Macro Result
PGN	0	50	0.03125
DATA	2

In this example, the macro result is 0.03125 and that number comes from 2/64 = 0.03125
So the next step should be 1000/50 = 20, 20*0.03125 = 0.625 so the result should be 0.625 (which would get added to the previous result of 625, bringing the total for these examples to 625.625).

Am I understanding the new process correctly?

[Excelmanineedhelp]

So for your first example I multiplied your macro result by 100 becasue I needed the macro result in percent, so 0.625 * 100 = 62.5 this is where i got the 62.5 and then I did 1000 * 62.5 = 62,500 this is where the 62,500 came from. So your second exmaple is spot on but all i did was convert .03125 to percent so it would be 3.125.
So the next step would be 1000/50=20, 20 * 3.125 = 62.5 then this is added with the previous result 62,500 and this total is 62562.5.

Hope this clears things up.

[tigeravatar]

Excelmanineedhelp,

Ok, I think I get it now. Give this a try:

Please Login or Register  to view this content.

[Excelmanineedhelp]

That work great, now I need the values in column J to be add up and total printed at the end of the column J on the same row with Total. But, the values before they are add need to be converted by the 1000/(values in column J)

In the example work book A134 and down:

So N/A = 1
(1000/20) + (1000/1) + (1000/1) + (1000/1) +(1000/100) = 3060

[tigeravatar]

Updated code:

Please Login or Register  to view this content.

[Excelmanineedhelp]

Once again perfect, Lasty need the 2 Total values to be divided so Ex. 59630305/121900 and answer to be printed on the Total row but the next column

[tigeravatar]

Updated code:

Please Login or Register  to view this content.

[Excelmanineedhelp]

Thank you once again Tigeravatar. You have helped me with everything thank you for patience and understnading. I don't know where I would be without your help.

[Excelmanineedhelp]

Hi Everyone,

This is Excelmanineedhelp, first I want to thank Tigeravatar for all the help. Every thing is fine with the marco, and I want every thing to be the same, all I need to do is update some parameter that will change two values, with the * in them, the row call Total. So, for the macro to get the Total for the column Macro Result, it has to mulitipy the values in column J and the data in Macro result and then add them together( see earlier post for more information). Now what I need the macro to do, is to still mulitipy the values in Column J and in the Macro results but now I need it only to mulitipy and add the rows that has a 8 on column I.

[tigeravatar]

Excelmanineedhelp,

Give this a try, I think it does what you're asking for, let me know if it gives incorrect results:

Please Login or Register  to view this content.

[Excelmanineedhelp]

Everything is correct and works fine. Thank you Tigeravatar.