Excel Question: Formating Like Data To Group Together

Split the data set into discrete elements. First element in column A, second in B, and so on. Text-to-columns should do this nicely. If your dataset is a fixed length for each element, easy peasy. This will let you sort by column A, then B, and so on. However you need to sort out your data.

Then, you write a macro. Set a comparison variable to the value in column A, row 1. Start rolling down the column. Compare the value in each cell as you get to it with the comparison variable. IF the value is equal to the comparison THEN delete the value in the cell. ELSE IF the comparison value is not equal to the value in the cell AND the cell above is blank, THEN set the comparison value to the current cell AND insert a blank row. Repeat until you reach a blank cell and stop.

EDIT: Hell, on second thought, you don't even have to do the macro part. After you break it into elements, put some conditional formatting on the first column so that if a cell is equal to the cell above it, the text color becomes the same as the background color. The data will still be there and you can manipulate it, you just won't see it. You won't get the space between groups, though.

EDIT: On third thought, a PivotTable should let you do what you're wanting to do as well if you'd like a nice automatically formatted report you can print out or pdf. You can group on the first element and set it up so you have groups you can expand or collapse with the first element at the top as a section heading and all of the other data elements listed one per row under that section heading. I'm not going to even try to explain those here, though. You're just going to have to learn by doing there.

If he wanted to delete the data and get the spacing, he would. I think I misunderstood, though, and he just wanted to not see the data. That conditional formatting thing should do the trick.

I'll assume your data currently has this in column A:

A123
A123
B123
B123
etc.

And what you want is this in columns A and B:

A123 123
A123 123
B123 123
B123 123

This is easy. In cell B1 type this formula: =MID(A1,2,LEN(A1))

... and then copy to the end of the column. You can then sort by column B to bring everything together.

Apologies for the confusion and delay. And this does have to be a macro because I’m putting some calculations into some of the columns

Every alpha-numeric is its own column.

So right now the information I have looks like this with | being a new column. :

A|1|2|3
A|5|3|2
B|3|2|3
C|5|4|2
C|3|1|2


The future state, which I do by hand now, looks like this

A|1|2|3
|5|3|2
|5|1| valid |50

B|3|2|3| False| -20

C|5|4|2
|3|1|2
|5|2| Valid| 15

Each item in column 1 will change every day and the number of times column 1 items repeats will change every day. So I need to come up with the formatting for the column one that will allow me to format it without having to go through the full process.

Apologies. Trying to keep it simple as possible

With each post you're adding another even more confusing element to your question.

A|1|2|3

quote:

---

|5|3|2

---

Is this supposed to be on the same row as the above? If not, why does it no longer have an A on its row?

quote:

---

|5|1| valid |50

---

Where is "valid" coming from and what does it mean? Plus I don't see these numbers anywhere in your raw dataset next to eachother. And what is the 50?

Do you mind sharing the spreadsheet? If not, I’ll try to help you out.

I’m just still not clear on what you want the macro to “execute”. You can write a macro to identify the “A”, then write/store all the data associated with every “A” in the set. It can write it out however you want. Add calculated numbers, etc., format font, cells, borders, etc. It would then move onto the next “B”.

I’d use a combination of do loops and if statements. I did something very similar for another poster for a time sheet, grouping raw time data into a formatted monthly report by person and day per project number. Macros can be finicky too depending on how they’re written. For example, if sorted and only two, it’d only need to check for repeats to the cell above it. If not sorted or variable repeats, it would need to check the entire set at each occurrence. Also changing rows/columns or moving cells can really screw up a macro, whereas formulas can be dynamic references.