ClioSport.net

Register a free account today to become a member!
Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

  • When you purchase through links on our site, we may earn an affiliate commission. Read more here.

Excel Help.



mace¬

ClioSport Club Member
  Clio
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:

excel.jpg


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,
 
  Cupra
- 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.
 
Last edited:

sbridgey

ClioSport Club Member
  disco 4, 182, Meglio
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¬

ClioSport Club Member
  Clio
You sir are a genius!

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

welshname

ClioSport Club Member
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.

for mace.JPG
 

mace¬

ClioSport Club Member
  Clio
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

ClioSport Club Member
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.
 
  Megane 225 F1
Seriously mace, apart from teaching me, being awesome and well hung, helping the poor etc...what Is your actual job?
 
  Cupra
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¬

ClioSport Club Member
  Clio
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.
 


Top