Condense rows add data to new columns?

[AnnaMae Bullock]

Hi, I have a spreadsheet with 1200 rows of rugs. There are perhaps only 300 rug styles, in varying sizes, HOWEVER the varying sizes are listed in separate rows with unique UPC codes and Item Numbers. (Enter my pain!) I need to condense the rows into one row, the initial row, and show all additional rug sizes in separate columns on the initial row. Is there a function that that would match the first few digits of the Item Number, for example 01184A, 01184R, 01184H, 01184S in the array (A2:A5), then copy the sizes from (C2:C5) into new columns named Size2, Size3, Size 4, etc? I need to triplicate this function for the corresponding range of UPC codes (F2:F5), and the Item Numbers beginning with A3 (A3:A5), as row 2 has the original rug information. Hopefully my intentions are clear. I do not know how to use pivot charts, CSE, VBA….but I can halfway follow functions, perform elementary nesting & super basic macro recording. I promised the completed spreadsheet to my boss by Friday, July 24, 2009. Thanks a bunch!
<o> </o>
I also have a “Caption” cell, column G, where a full description of the rug goes and it’d be awesome if there was a “mailmerge feature” that could incorporate descriptors from the same row of the spreadsheet into the caption cell that is formatted like this:
<o> </o>
***********************************************************************
Shaggy Raggy Lavender Shag Rug<o></o>
<o> </o>
Color: Lavender<o></o>
<o> </o>
Type: Shag<o></o>
<o> </o>
Material: Cotton <st1lace w:st="on">Jersey</st1lace><o></o>
<o> </o>
Size: 22 x 34 feet, 2.8 x 4.8 feet, 4.7 x 7.7 feet, and 4 x 4 feet<o></o>
<o> </o>
Item No: 767843196288, 767843196283, 767843200560, 767843196290, 02201A, 02201B, 02201D, 02201R<o></o>
<o> </o>
***********************************************************************
I’m thinking of simply running a merge in MS Word then cut/paste. But I’m just wondering if there is a “work-smart” approach. Is it possible to set up the merge main document directly in cell (G)?
<o> </o>
Many thanks!

 

[MickG]

Hi, A before and after example of your sheet, would be helpful for the first bit !!
Regards Mick

 

[hiker95]

AnnaMae Bullock,

Welcome to the MrExcel board.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

 

[narelle_s]

I have a similar issue, so was hoping to see a follow up to this question.

Here is my screenshot :

http://farm3.static.flickr.com/2642/3745079850_5cfc83d3b8_o.jpg

The first section shows multiple rows of data, but the Incident Numbers are repeated for various breaches.

Is it possible to add new columns to the top section, and line up the Breache, Met, Code next to each other, so that there is only one incident per row. Which I have shown in the second section.

The top section has a LOT more columns included, but this is just for illustrative purposes.

Appreciate your thoughts!

 

[MickG]

Do you mean the TOP section has a Lot more Rows, You say Columns ??
Are the all the types of Breaches shown.
What happens in situation like 1123 That appears twice with BREACH "OnSite" Both showing "Yes" under "Met, But what if one said "Yes" and the other "No".
Regards Mick

 

[narelle_s]

Thank you for your response Mick.

I do mean columns, yes. I have just created a sample docoment that does not contain real data - I just wanted to illustrate so that I can find out if this is even possible. I don't want to ask anyone to go to any great trouble to write a macro for this or anything

Oh I also made a mistake there, it would only ever have one (at most) instance of each type of breach (so I would only need one column for each type of Breach, and one corresponding column for each code).

Thanks
Narelle

 

[MickG]

Hi, Am I missing something. At the Moment in the first set of data (Top Section) you have 4 columns, These columns can provide all the information For the layout in the 2nd set of Data (second section), So what will be in the other Columns in Data 1.
Also, you have not said if there all any More Instances of "Breaches" than the list already shows.
On the basis of what you have at the moment some code should do what you want, but its obviously easier to have the right information at the start.
Regards Mick