How to Append the Data to the Master Table

[Shiva]

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 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

 

[Ashish Mathur]

Hi Shiva,

I may be able to help you and understand the questin better if you can mail
me the spreadsheet at (e-mail address removed)

Regards,

Ashish Mathur

 

[Shiva]

Hi all,

Can anybody help on this.


Thanks in advance

Regards,

Shiv

 

[DOR]

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.

 

[Shiva]

Thanks DOR,

Excellent it is working!!!!

I would like to contnue the same issue. How to modify this function if
I have some n numbers of constant columns for particular size range
like colour and further X numbers of products with Y numbers of
product attributes.

Thanks you very much

Regards,

Shiva

 

[DOR]

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$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

 

[DOR]

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

 

[Shiva]

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