none
How to transport some column only RRS feed

  • Question

  • Hi Experts,

    I have a Sales Forecast worksheet, the original layout is this. (Jan..Apr is every month forecast amount for every A column. A is "item", B is "Sales Code", C is "Region".)

    A1 B1 C1 Jan Feb Mar Apr
    A2 B2 C2 Jan Feb Mar Apr
    A3 B3 C3 Jan Feb Mar Apr
    A4 B4 C4 Jan Feb Mar Apr

    For import into Navision, I need to change layout like this.

    A1 B1 C1 Jan
    A1 B1 C1 Feb
    A1 B1 C1 Mar
    A1 B1 C1 Apr
    A2 B2 C2 Jan
    A2 B2 C2 Feb
    A2 B2 C2 Mar
    A2 B2 C2 Apr
    A3 B3 C3 Jan
    A3 B3 C3 Feb
    A3 B3 C3 Mar
    A3 B3 C3 Apr
    A4 B4 C4 Jan
    A4 B4 C4 Feb
    A4 B4 C4 Mar
    A4 B4 C4 Apr

    Does anybody can help to let me know how to do it or how to add VBA to process it ?

    ps. Total I have 2000 row.

    Thanks


    James Liang

    Friday, May 15, 2015 3:42 PM

Answers

  • Lan,

      I create 2 tables, the 1st table field same as worksheet but add SN, I can use SSIS to import worksheet into this table. The 2nd table is SN, Item, Sales Code, Region, Date, Amount 6 column only. now, I create a query

    1. Count table1 .

    2. use this count for loop.

    3. in loop, I read table1, key is SN

    4. Insert into table2, but in there, I have 12 times "Insert" command, the 1st insert "Date" is 1/1/2015, the 2nd insert "Date" is 2/1/2015....

    5.  after loop, the table2 is working.

    Thanks 


    James Liang

    • Marked as answer by L.HlModerator Tuesday, June 2, 2015 3:26 AM
    Wednesday, May 27, 2015 3:39 PM

All replies

  • Hi James,

    Please upload a sample file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

    Andreas.

    Saturday, May 16, 2015 3:33 PM
  • Andreas,

      Thanks your reply. I found to use SQL is easier than VBA.

    James


    James Liang

    Monday, May 18, 2015 5:35 PM
  • Andreas,

      Thanks your reply. I found to use SQL is easier than VBA.

    James


    James Liang


    Hi,

    Glad you have found a workaround about this, could you please share you solution with others, so it will be helpful when they encounter the similar situation?

    Thanks

    Best Regards,

    Lan


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, May 19, 2015 5:41 AM
    Moderator
  • Lan,

      I create 2 tables, the 1st table field same as worksheet but add SN, I can use SSIS to import worksheet into this table. The 2nd table is SN, Item, Sales Code, Region, Date, Amount 6 column only. now, I create a query

    1. Count table1 .

    2. use this count for loop.

    3. in loop, I read table1, key is SN

    4. Insert into table2, but in there, I have 12 times "Insert" command, the 1st insert "Date" is 1/1/2015, the 2nd insert "Date" is 2/1/2015....

    5.  after loop, the table2 is working.

    Thanks 


    James Liang

    • Marked as answer by L.HlModerator Tuesday, June 2, 2015 3:26 AM
    Wednesday, May 27, 2015 3:39 PM