Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Macro - multi step, delete based on value, subtotal, concatenate the 2nd character

Posted on 2015-01-21
10
Medium Priority
?
199 Views
Last Modified: 2015-01-23
Hello:

I need help with a macro that already has a lot of action, but needs more.   I am attaching a sample.   In my sample I need to search column J and clear anything in it that does not begin with an E, if I clear that cell, I also need to clear the corresponding cell in F.  this will need to loop thru until there is no more data in C.  

Next I need to subtotal the F's  based on a change in C and put the result in G on what would be the first line of data for that subset.  In my example G2, G6, G9, etc will have the subtotals.  (if it is easier and this all goes on a bottom line, I can fill the remaining columns down)

Then I need to use the concatenate function to pull the 2nd character from groups the J cells and also put it on the first line of the subset.   Line 2 is what it is supposed to look like.

All this will need to loop thru until there is no more data.  Line A,  B and C will be full of data until the end.  

You will notice there will be some lines where people do not have anything in G or J - this is correct and will happen throughout the spreadsheet.  

I know this is a lot to ask, but I know there are some awesome excel people on this forum, so I'm shooting the moon.  

THANKS ahead of time for any effort you make on this.
Jen
sample-data.xlsx
0
Comment
Question by:jenit
  • 5
  • 5
10 Comments
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40563017
I can probably create the macro for you from scratch, but you seem to be saying that you have a macro that does some of it, but as the file you posted is xlsx format, all macros were removed.
0
 

Author Comment

by:jenit
ID: 40563060
Sorry I was unclear.  The macro I'm talking about has already gotten the sheet to this point.  I don't have a macro that does any thing after the sheet looks like this.
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40563084
OK, no problem. Be back soon.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:jenit
ID: 40563130
that is awesome
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40563160
Run the "Multi" macro in the attached workbook. I not sure at all that I got what goes in column K correct so please explain further.
Q-28601215.xlsm
0
 

Author Comment

by:jenit
ID: 40563276
Wow, that is a great start.  The sub totals work perfectly.  I'm excited as I have another worksheet I will be able to use that on as well.

K column needs to have the 2nd character of each cell in the set (before a change in C).  EL is the result that is returned below, it should be MDVL
EMEO      EL
EDES      
EVES      
ELES      

I have to sign off for the night for kid activities.  But will be back at it early tomorrow.  THANKS for your help on this.
0
 
LVL 49

Accepted Solution

by:
Martin Liss earned 2000 total points
ID: 40563314
Ok, that explanation helps.
Q-28601215a.xlsm
0
 

Author Closing Comment

by:jenit
ID: 40564204
I would give you an A+ if I could.  That is truly awesome!   The only funky is it puts extra characters in K if there was no data in J, it continues to enter the characters from the LAST calculated entry -  I'm not concerned with that as I'll add to the macro a function to find and clear those cells.   This works Perfect for what I am doing.  

Thanks, each time I use this forum for a new macro, I gain experience and code that I can use in other situations.  

Your response was right on target and so quick - a BIG THANKS!
Jen
0
 
LVL 49

Expert Comment

by:Martin Liss
ID: 40564442
Try this.
Q-28601215b.xlsm
0
 

Author Comment

by:jenit
ID: 40566386
That does it.  Thanks again for all your help.   To all who are interested in this macro - it is clean and does some great things.

It will subtotal and put the result in a different column
It will pull the 2nd character out of a string and create a new value with those characters until it sees a blank, then it starts again.  
It will clear cells based on a specific character and also clear their corresponding cells a few columns over.
0

Featured Post

[Webinar] Cloud Security

In this webinar you will learn:

-Why existing firewall and DMZ architectures are not suited for securing cloud applications
-How to make your enterprise “Cloud Ready”, and fix your aging DMZ architecture
-How to transform your enterprise and become a Cloud Enabler

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
When asking a question in a forum or creating documentation, screenshots are vital tools that can convey a lot more information and save you and your reader a lot of time
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question