Formatting data in Excel

[EpicSnail]

Hey all!
Long time reader, first time poster!

have a bit of a weird question and was wondering if somebody could help me out?

I have had some data extracted in the format

Computer 1
CPU: 2ghz
Hard Drive: 500GB
RAM: 6GB
Computer 2
CPU: 2.2ghz
Hard Drive: 500GB
RAM: 6GB


ETC (and there are about 10,000 records in this format!)

I have no Idea how to get it in the format

Computer name: CPU: Hard Drive: RAM:
Computer 1 2GHz 500GB 6GB
Computer 2 2.2GHz 500GB 6GB


anybody got any ideas?

Thanks in advance for your help

[NBVC]

Welcome to the fourm,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

[EpicSnail]

oops, really sorry will change that now!

[NBVC]

Thanks for fixing that.

Say that data is in range A1:A8

Now in B2 enter:

=INDEX($A$1:$A$8,4*(ROWS($A$1:$A1)-1)+1)

adjust the A1:A8 to include full range..

copy that across to D1 and change the first $A$1 to $A$2 in B1, $A$3 in C1 and $A$4 in D1

Then copy all 4 down as far as you need to get all your data

[JBeaucaire]

If a macro is ok, try this:

Please Login or Register  to view this content.

How/Where to install the macro:

1. Open up your workbook
2. Get into VB Editor (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste in your code (given above)
5. Get out of VBA (Press Alt+Q)
6. Save your sheet

The macro is installed and ready to use. Press Alt-F8 and select it from the macro list.

Remove the line of code marked in red if you don't want the original column of data in column A to be removed.

[EpicSnail]

Thanks for that
One thing I forgot to mention (im feeling rather dense today)

Sometimes the data may include
VRAM: 128MB
or
NIC: 10/100/1000

or similar

Doh!

any ideas on that one?

Cheers

[JBeaucaire]

Like so:

Please Login or Register  to view this content.

As you can see we're testing the first 3 letters of each cell. You can expand this as much as you wish by adjusting the red values, or adding to the lists as shown.

[EpicSnail]

ahhh thats brilliant!, thanks to both of you

[JBeaucaire]

I corrected an error in the added sections above, be sure to get the corrected code.

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].