Microsoft excel query

heppy23

I'm trying to do some work on a big list of stock items.

The list is of various tags that apply to that item.

At the moment the file looks like:-

Item1,green,sock,large
Item2,blue,hat,small

How can I get the file to look like

Item1,green
Item1,sock
Item1,large
Item2,blue
Item2,hat
Item2,small

I need to be able to do it as easily as possible as it's a very large file I'm working on.

lesalanos

what is in each cell now.. is it Item1,green,sock,large in one cell or spread across4?

heppy23

spread across 4, I need it spread across 2.

Thanks.

heppy23

Can anyone help with this?

jbreckmckye

There might be ways of doing it with XML and XSLT, but that may be a bit too involving...

KEM

Assuming the info for item 1 is in cells A1, A2, A3 and A4 you could try:

=A1&A2

=A1&A3

=A1&A4

or look up the use of concatenate in Excel as I think that will do what you want.

HTH

Stompa

It's a simple copy and paste job.

Insert some extra columns, and copy and paste the data from your items column into them, so you end up with:

Item1,green,Item1,sock,Item1,large
Item2,blue,Item2,hat,Item2,small
etc.

Now just cut and paste the second and third pairs of columns on to the end of the first pair of columns. Job done.

If the order in which they appear is important to you, you'll need to temporarily insert additional columns containing order indexes and sort by those before deleting.

heppy23

Stompa wrote: »

It's a simple copy and paste job.

Insert some extra columns, and copy and paste the data from your items column into them, so you end up with:

Item1,green,Item1,sock,Item1,large
Item2,blue,Item2,hat,Item2,small
etc.

Now just cut and paste the second and third pairs of columns on to the end of the first pair of columns. Job done.

If the order in which they appear is important to you, you'll need to temporarily insert additional columns containing order indexes and sort by those before deleting.

I've been doing it that way but some of the files are 60+ columns wide and I end up losing track.

SpammyTheSpammer

The suggested solution should work but I do not understand what you mean by " some of the files are 60+ columns wide" ?

Stompa

heppy23 wrote: »

I've been doing it that way but some of the files are 60+ columns wide and I end up losing track.

Ah OK, I didn't realise you were talking about that many columns.

I guess you could try adding an additional column containing a character that's not used elsewhere in the data (say @). So you end up with:

Item1,green,@,Item1,sock,@,Item1,large
Item2,blue,@,Item2,hat,@,Item2,small

now save it as a text file, load it into a text editor, replace all occurrences of ',@,' with a carriage return, and reimport the modified file back into Excel.

It would also be easier to let Excel do the hard work when inserting the extra columns, so add them all to the right, with an additional row somewhere containing indexes indicating the final order, then sort by that additional row to get them in the right place.

hubert_cumberdale

can you not do a CSV export or something like that



mmmmm thats a tricky one i have been trying it and it may take some time
would be easier to get it into a database