Complex Filter/Sorting Help Needed (VBA knowledge needed)

[dfxryanjr]

My task/problem right now is to figure out how to group a large pool of data that has a lot of information in one cell.
For example, in column A-C will look like this:
10 pink balloons version 1
10 pink balloons version 2
12 blue balloons version 1

The number, color, and version are all important to me but grouped in the same column. Every week I got a large excel sheet emailed to me in this format and I would like to write a macro to sort it so that it is easily digestible. I have limited VBA programming knowledge, but so far what I was thinking was to record a macro with a bunch of different Conditional Formatting rules in order to color code the cells. From there I hope to sort the data based on the color of the cell.

Specifically, for all red cells in column A, I need to sum the values of their corresponding cell D. So if A1 and A20 are red, I need to sum D1 and D20..

All help is greatly appreciated.

[mikerickson]

Could you attach an example workbook showing how you get it and how you want it after the macro runs?
I'm confused, you mention that your example above was in columns A-C but then said "grouped in the same column"

Can we assume that there are also pink and blue tennis shoes in addition to balloons?


Would "everything before the first space in column A, everything after the last space in column C, everything else in B" get the desired result?

[dfxryanjr]

Sorry, I meant to say A1-a3. Not a-c. So each cell in Column A has the "10 balloons..." Information

[dfxryanjr]

And to your other questions,
Yes, there can be shoes as well as balloons.
But you also asked if dividing things by spaces would get the desired result. Short answer is no, sometimes its formatted weird and there might be multiple spaces. But in theory if i know how many spaces there are, your concept might work.

Thanks

[mikerickson]

It looks like a typical entry would be [numeral][some spaces][text][some spaces][numeral] and you want three columns: [numeral], [text], [numeral].

If so, try putting
=TRIM(LEFT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255)) in B1
=TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",255)),255)) in D1
=TRIM(SUBSTITUTE((LEFT(TRIM(A1),LEN(TRIM(A1))-LEN(D1))),B1,"",1)) in C1 and dragging down.

Does that give the desired result? Do you need help automating that?

[dfxryanjr]

Actually my data is a little more complicated than I made it sound.

So I am trying to process a pipe order. In column A is the description of the pipe, however, there are multiple different criteria of identification all within column A.

ExA1-A2.
45" 23# S22 Good
10 3/4" 44# J45 Bad

This tells me the Length, Weight, Make, and Status. From here, I have used a conditional format ("Text that contains") to distinguish cells via length (which is the most important). So anything with 45" will be blue. Anything with 10 3/4" will be red, etc..

But from here I am trying to make a summary output macro that will give a sum of the corresponding D column, for every different colored cell.

Example,if 10 cells are blue, say a1-a10, (due to having 10 different makes of 10" pipe), I want to then have a cell that sums d1-d10. The D column contains the total number of feet of each make of pipe.

Furthermore, Ideally I would be able to summarize info more specifically, meaning, within my 10" pipe, I can say how many feet (column D) I have of 10" pipe that is GOOD, or that is BAD.

As you can see my original ballon example wasn't quite as complex, but I think the same logic could be used. The toughest part for me is to correlate column D to a pre-sorted list. I have been using the countif function to total the number of cells containing certain words/letters, but this only gets me so far.

[mikerickson]

Attach a workbook showing a good sample of the kind of data that you are getting and how you want that data to be split into columns.

[dfxryanjr]

I made a macro that was able to solve the problem sufficiently using a lot of SUMIF statements, but I acknowledge this isn't necessarily the best way to go about solving this problem, nor the most sustainable way. If you could take a look at the attached data and tell me what you would do I'd appreciate that a lot.