How to Append the Data to the Master Table

There are two tables one is Master table and other is Specification table.
The Specification table contains the multiple columns colour, size, product1, product1-material, product2, product2-material, product3, product3-material from A:H in sheet1. The data type is, colour and products are constant for whole size range and the products material will change for particular size ranges. There will be different colours product too.

The Master table contains the columns colour, size, product, product material. A:D in sheet2.Now the question is how to append the data from the Specification table to Master table i.e append data from columns colour to colour, size to size, product1 to product, product1-material to product material and similarly for other products data also from Specification table to Master table.

I would be grateful if anybody can suggest how to go on this.

Regards,

Shiva

Hi Shiva,

I may be able to help you and understand the questin better if you can mail
me the spreadsheet at [email protected]

Regards,

Ashish Mathur

"Shiva" wrote:

>
> There are two tables one is Master table and other is Specification
> table.
> The Specification table contains the multiple columns colour, size,
> product1, product1-material, product2, product2-material, product3,
> product3-material from A:H in sheet1. The data type is, colour and
> products are constant for whole size range and the products material
> will change for particular size ranges. There will be different colours
> product too.
>
> The Master table contains the columns colour, size, product, product
> material. A:D in sheet2.Now the question is how to append the data from
> the Specification table to Master table i.e append data from columns
> colour to colour, size to size, product1 to product, product1-material
> to product material and similarly for other products data also from
> Specification table to Master table.
>
> I would be grateful if anybody can suggest how to go on this.
>
> Regards,
>
> Shiva
>
>
> --
> Shiva
> ------------------------------------------------------------------------
> Shiva's Profile: http://www.excelforum.com/member.php...o&userid=28449
> View this thread: http://www.excelforum.com/showthread...hreadid=482576
>
>

Hi all,

Can anybody help on this.


Thanks in advance

Regards,

Shiva

Shiva,

If I interpret your requirements properly try the following:

Assuming your data starts in row 2 of sheet 1, and you want to append
the data starting in row 2 of sheet2. (See below if you want to start
in different rows).

Enter in sheet 2:

Col A: =OFFSET(Sheet1!A$2:A$2,INT((ROW()-ROW(Sheet2!$2:$2))/3),0) and
drag/copy it to col B

Col C:
=OFFSET(Sheet1!C$2:C$2,INT((ROW()-ROW(Sheet2!$2:$2))/3),MOD(ROW(),3)*2)
and drag/copy to col D

Col E: =MOD(ROW(),3)+1 (gives you number of the product suffix from
each row in sheet 1)

If your data in sheet 1 starts in other than row 2, change all the
references to Sheet1!..$2:$2 to $N:$N, where N is the number of the row
in which the data starts.

Similarly, if you want to append in sheet 2 starting at a different
row, change the references to Sheet2!ROW($2:$2) to the other row.

Glad to know it worked OK ...

I am assuming that the unique identifier for each row in sheet 1 is
colour, and that you have one column for colour, N columns of other
independent attributes, and X groups of product information, each Y
columns wide. I believe the following will work (note that I am simply
changing the values in my original instructions to you to the values
N,X and Y - I have done limited testing - I am assuming you will do
that <g>)



In sheet 2

Col A: =OFFSET(Sheet1!A$2:A$2,INT((ROW()-ROW(Sheet2!$2:$2))/X),0) and
drag/copy it an additional N columns


In the next column (assume it is column P):
=OFFSET(Sheet1!P$2:P$2,INT((ROW()-ROW(Sheet2!$2:$2))/X),MOD(ROW(),X)*(Y+1))
and drag/copy an additional Y columns

Next available column: =MOD(ROW(),X)+1

In case I made any typos above, the following worked well for 1 colour,
3 attributes and 4 products with 2 materials each.

Col A: =OFFSET(Sheet1!A$2:A$2,INT((ROW()-ROW(Sheet2!$2:$2))/4),0)
dragged to col E
Col E:
=OFFSET(Sheet1!E$2:E$2,INT((ROW()-ROW(Sheet2!$2:$2))/4),MOD(ROW(),4)*3)
dragged to col G
Col: H: =MOD(ROW(),4)+1

HTH

I made a slight error in my previous reply - in the actual example I
used, I should have said with regard to the entry in col A that it was
dragged to column D, not E. (it was originally E in my first test, but
I changed the variable values to make them all different and forgot to
change my text ...)

Sorry about that.

BTW, I have inferred that you want one row in the Master table for each
product occurrence in the specification table, drawing the attributes
for each product from the first N columns of the row containing that
product in the specification table. I hope this is correct, if not,
let me know.

Declan

Hi Declan,
It’s great, working perfectly. I tested it for other cases too. Your assumptions are absolutely correct as per my requirements.
I have a huge database, which will be in continuous updation and additions of the products data. It was consuming hell lot of my time in maintaining this database.


Once again, Thank you for your time and help.

Declan, I need your email ID. Future in case any help is required I will contact you.

Regards,

Shiva