Two Rows Into One?

[Zoskie]

Hi guys, new to the forum so please be gentle with me!

I'm currently trying to work out the best way of condensing data on a worksheet. At present each individual product (there's 1164 of them in total) occupy's two rows - one containing a colour name and the other containing an image ref. I'm looking for the easiest way to coverge the two rows of data into one, so that each product's data is then only on one line but on a seperate worksheet.

To complicate matters a little further, when the data is transposed it needs to be alternate.

Example:

Before
PRODUCTS_5400_30959.jpg GM013 Black Light Blue Navy
OPTIONS_50636_38898.jpg OPTIONS_50637_38899.jpg OPTIONS_50638_38900.jpg

After
PRODUCTS_5400_30959.jpg GM013 Black OPTIONS_50636_38898.jpg Light Blue OPTIONS_50637_38899.jpg Navy OPTIONS_50638_38900.jpg

As already mentioned, there's a total of 1164 products (so currently 2328 rows of data) which i need to manipulate, so the more automated the better.

Thanks in advance

[Andrew-R]

If your data starts in cell A1 then try this formula:

=OFFSET($A$1,MOD((COLUMN(A:A)-1),2),INT((COLUMN(A:A)-1)/2))

As you drag it across it will alternate rows to product the data you want. The section in bold is the bit that needs to be changed to work with data in a different range of cells.

[Domski]

I think your only going to be able to achieve this with VBA. The sticking point will be knowing if a colour consists of one or two words so as to know where to insert the options text. The easiest way round this would be to start off changing all colours consisting of two words to one like Light_Blue.

Dom

[Zoskie]

Hi guys, new to the forum so please be gentle with me!

I'm currently trying to work out the best way of condensing data on a worksheet. At present each individual product (there's 1164 of them in total) occupy's two rows - one containing a colour name and the other containing an image ref. I'm looking for the easiest way to coverge the two rows of data into one, so that each product's data is then only on one line but on a seperate worksheet.

To complicate matters a little further, when the data is transposed it needs to be alternate.

Example:

Before
PRODUCTS_5400_30959.jpg GM013 Black Light Blue Navy
OPTIONS_50636_38898.jpg OPTIONS_50637_38899.jpg OPTIONS_50638_38900.jpg

After
PRODUCTS_5400_30959.jpg GM013 Black OPTIONS_50636_38898.jpg Light Blue OPTIONS_50637_38899.jpg Navy OPTIONS_50638_38900.jpg

As already mentioned, there's a total of 1164 products (so currently 2328 rows of data) which i need to manipulate, so the more automated the better.

Thanks in advance

Sorry guys, my example wasn't very clear on reviewing.

The information for each product is on two seperate rows AND multiple columns - as per attached. Excel Test.xlsx

[Andrew-R]

If you modify my formula to:

=OFFSET(Sheet1!$A1,MOD((COLUMN(A:A)-1),2),INT((COLUMN(A:A)-1)/2))

And paste that into cell A1 of sheet 2 in your example, and then drag across, it should work fine.

[Zoskie]

If you modify my formula to:

=OFFSET(Sheet1!$A1,MOD((COLUMN(A:A)-1),2),INT((COLUMN(A:A)-1)/2))

And paste that into cell A1 of sheet 2 in your example, and then drag across, it should work fine.

Many thanks for your help Andrew-R.

Would the formula you're suggesting result in this: Excel Test.xlsx ?

Again thanks in advance,

Zoskie

[Andrew-R]

Not quite.

The formula to get you that result is:

=IF(COLUMN(A:A)<3,OFFSET(Sheet1!$A$1,INT(ROW(1:1)-1)*2,COLUMN(A:A)-1),OFFSET(Sheet1!$C$1,MOD(COLUMN(A:A)-3,2)+((ROW(1:1)-1)*2),INT((COLUMN(A:A)-3)/2)))

In cell A1 of sheet2, copied across and down.

[Zoskie]

That's absolutely fantastic!!

Many, many thanks for your help and assistance, that's exactly what i've been looking for and trying to implement for days.

Just out of curiosity .... obviously the rows end up at different lengths as products have differing SKU's (in this case colours) which results in a number of cells containing "0". Can the formula be modified in any way to ignore "0" rated cells, therefore leaving them blank?

Once again many thanks,

Zoskie

[Andrew-R]

Yes, we can do that, but it's not pretty ...

=IF(COLUMN(A:A)<3,OFFSET(Sheet1!$A$1,INT(ROW(1:1)-1)*2,COLUMN(A:A)-1),IF(OFFSET(Sheet1!$C$1,MOD(COLUMN(A:A)-3,2)+((ROW(1:1)-1)*2),INT((COLUMN(A:A)-3)/2))="","",OFFSET(Sheet1!$C$1,MOD(COLUMN(A:A)-3,2)+((ROW(1:1)-1)*2),INT((COLUMN(A:A)-3)/2))))

Should be it.

[Zoskie]

Yes, we can do that, but it's not pretty ...

It doesn't have to be prety, as long as it gets the job done ...... which it has!

Yet again Andrew, many thanks for all your help and assistance in offering a solution to my problem. If only i'd registered on the forum weeks ago!!!

Zoskie