Microsoft Excel List Query

barneysplash

Hello all

I have a list in Excel:

John Smith
Peter Jones
Mary Carter

But I need it in the format :

John Smith
John Smith
John Smith
John Smith
John Smith
Peter Jones
Peter Jones
Peter Jones
Peter Jones
Peter Jones
Mary Carter
Mary Carter
Mary Carter
Mary Carter
Mary Carter

Anyone know a formula or formatting trick to do this quickly?

I have several hundred such entries in the list.

Thanks in advance.

MiCr0

is there any logic to the sort that you want?

barneysplash

I found a way of doing it without macros or formulas :

To make a list like this:

John Smith
Peter Jones
Mary Carter

into this:

John Smith
John Smith
John Smith
John Smith
John Smith
Peter Jones
Peter Jones
Peter Jones
Peter Jones
Peter Jones
Mary Carter
Mary Carter
Mary Carter
Mary Carter
Mary Carter





I took the list:

John Smith
Peter Jones
Mary Carter


etc...

and I copied it to the clipboard.

Then I clicked on the first empty cell underneath my list.

Then I pasted the list I had copied to the clipboard.

So then I had 2 copies of the lists.

Then I repeated the pasting procedure into the first
empty cell underneath this list.

I did this twice more, and then I had 5 copies of the list
in a single column.


Then I clicked on the Sort A to Z function on the standard toolbar.

This looks like a capital letter A with an arrow down to a capital Z.

Excel then sorted the list alphabetically. So from :

John Smith
Peter Jones
Mary Carter
John Smith
Peter Jones
Mary Carter
John Smith
Peter Jones
Mary Carter
John Smith
Peter Jones
Mary Carter
John Smith
Peter Jones
Mary Carter

I get :


John Smith
John Smith
John Smith
John Smith
John Smith
Peter Jones
Peter Jones
Peter Jones
Peter Jones
Peter Jones
Mary Carter
Mary Carter
Mary Carter
Mary Carter
Mary Carter

Happy days !


Using Notpad with Excel to manage lists

Windows comes with a basic text editor built-in. It is called Notepad.

It does not have any of the complicated features that Microsoft Word offers.
You cannot use colour or fonts in Notepad.
But Notepad is very quick and reliable.

Notepad used the .txt format to store files. These are simple files.
If you copy and paste text from a webpage to Notepad, it will display
the text, but will remove all of the HTML formatting, colour text used.

If you copy and paste text from Microsoft Word, for example, Notepad
will display only the text and remove any of the font changes
or text formattting you used in Word.

This is very useful if you want to take a copy of the text, but do not want
any of the colour or fonts or different formatting.

The .txt file format is understood by Word and Excel, so you can open
. txt files in Word and Excel no problem.

I like to use notepad for lots of little things, but the thing I use
it most for us as a quick way to manage lists of data you need to push in and out of Excel.

Here's how to make a sample list for use in Excel with Notepad :
You can open Notepad by clicking Start - Programs - Accessories - Notepad

or you can make a new notepad file using this way:

If you right click on the desktop and move the cursor
down to New, then move the cursor over to
"New Text Document"


A new text document will appear on the desktop.

This document is a small white rectangle that looks
like a small notepad.

The name part of this file will be highlighted in blue, you
can type a new name in here, but you don't need to in this example,
so just press Enter to confirm that you want to call
the document "New Text Document"

Double click on the file, and a blank text document will open.

You can then type in or paste in 1 copy of your list.

I like to use notepad for this because you can quickly make
a long list of a small number of entries. So if you list is
something like :

IRE
UK
DE
FR
AUS
USA

Once you've written or pasted the text, select the text
and copy it.

Make sure to copy the line under the last entry in your list,
even though it does not have any text written in it.

This will make sure that Notepad will copy your cells correctly.

You can then paste it under the original text as many
and build up a long list of entries.

You can then sort these in Excel using the above procedure.

Shortcut Keys
For copying and pasting, I like to use the shortcut keys.

You can use these instead of going to the Edit menu at
the top of the window.

Once you select some text, press and hold the CONTROL key
This is usually in the bottom left of the keyboard with "Ctrl"
written on it.


Now press the C key. The text you highlihgted will be copied to
the clipboard.


Now move the cursor to where you want to paste the text.

Agaiin, press and hold the Control key. This time press the V key.

Your copied text will appear!

If you use the press and hold the Control key and press the A key,
all the text in the document you are working on will be highlighted.

You can then keep your finger on the control key and press C to
copy this text. Then you can open where you want the text to go,
and press control and the V.

To cut text, that is, to remove it completely from the document, press
Control and X.

I keep my little finger near the control key when I am typing or working
in Excel. It is very useful to be able to quickly copy, cut and paste text
around your document, without having to take your hand off the keyboard
to use the mouse.