Excel Help.

[mace¬]

I wonder if anyone can help me.......

I have an excel spreadsheet In column A I have codes and in column B I have comments. For example:

What I need to do is sort by the code in alphabetical order keeping the comments attached? Problem is the only way we could get the data in was to have the comment info on different rows

Roughly 20,000 rows with 4 thousand codes.
Ta,

 

[Andy.]

- Put an autofilter on the top row.
- Select "only blanks" from column A
- Put the formula =A2 in cell A3, then copy it and paste down the rest of the blanks.
- Sort by Column A, the comments should all stay in the correct order with the Code in alphabetical order.

 

[sbridgey]

Are the comments all in the same cell technically or have they gone to the next cell once one is full, if u know what I mean?

 

[mace¬]

You sir are a genius!

One more thing, once sorted into alphabetical anyway of removing the duplicate codes in column A we created?

 

[mace¬]

Are the comments all in the same cell technically or have they gone to the next cell once one is full, if u know what I mean?

Were in the next cell, thats what confused me.

Andy sorted it!

 

[LIL B]

Those comments cannot be correct, bob is a nice fella.

 

[mace¬]

Those comments cannot be correct, bob is a nice fella.

Noone said he wasn't, and he has one hell of a knob.

 

[welshname]

As a future note i would format the comment cells so that you only have 1 cell per comment per code. Just format the cell for "text wrapping" this will allow you to make the cell "taller" rather than "wider" and mean sorting them in future will be a lot easier.

 

[mace¬]

As a future note i would format the comment cells so that you only have 1 cell per comment per code. Just format the cell for "text wrapping" this will allow you to make the cell "taller" rather than "wider" and mean sorting them in future will be a lot easier.

View attachment 61278

The way we extracted the data from the system didn't allow us to do that. Data is sort of stored on an array and we had no simple way of stringing it together and then extracting it on one line.

Couldn't be assed to write the code to write it directly to a file so it had to be a copy and paste job.

 

[welshname]

The way we extracted the data from the system didn't allow us to do that. Data is sort of stored on an array and we had no simple way of stringing it together and then extracting it on one line.

Couldn't be assed to write the code to write it directly to a file so it had to be a copy and paste job.

Fair point. Whats had already been said above is the easiest solution then.

 

[Will.]

Seriously mace, apart from teaching me, being awesome and well hung, helping the poor etc...what Is your actual job?

 

[Andy.]

You sir are a genius!

One more thing, once sorted into alphabetical anyway of removing the duplicate codes in column A we created?

Yes. Before you start, from the top:

- insert a new column infront of A and name it "Temp".
- Put an autofilter on the top row.
- unselect "blanks" from column B
- You should now only see the Code and the first line of comments. Type "keep" in A2 and paste down to the bottom.
- reSelect "blanks from column B so that all data is visible.

This will add "Keep" next to each "Code" value.

then what I said before (changing A-B as you have inserted an extra column):

- Put an autofilter on the top row.
- Select "only blanks" from column A
- Put the formula =A2 in cell A3, then copy it and paste down the rest of the blanks.
- Sort by Column A, the comments should all stay in the correct order with the Code in alphabetical order.

Next bit:

- Using the autofilter, only select the blanks from column A
- Delete all the text from Column B
- Turn off the autofilter.
- Delete column A

There must be an easier way, but I always use the autofilter work arounds for stuff as they are fairly quick.

 

[mace¬]

Seriously mace, apart from teaching me, being awesome and well hung, helping the poor etc...what Is your actual job?

Penquin picker upper.

Yes. Before you start, from the top:

- insert a new column infront of A and name it "Temp".
- Put an autofilter on the top row.
- unselect "blanks" from column B
- You should now only see the Code and the first line of comments. Type "keep" in A2 and paste down to the bottom.
- reSelect "blanks from column B so that all data is visible.

This will add "Keep" next to each "Code" value.

then what I said before (changing A-B as you have inserted an extra column):

- Put an autofilter on the top row.
- Select "only blanks" from column A
- Put the formula =A2 in cell A3, then copy it and paste down the rest of the blanks.
- Sort by Column A, the comments should all stay in the correct order with the Code in alphabetical order.

Next bit:

- Using the autofilter, only select the blanks from column A
- Delete all the text from Column B
- Turn off the autofilter.
- Delete column A

There must be an easier way, but I always use the autofilter work arounds for stuff as they are fairly quick.

Thank you. Another happy customer.

 

[Andy.]

Welcome.