VBA Count Totals and paste relevant number

  • Hello



    each week a spreadsheet of numbers is uploaded. the list length varies but the terms and conditions do not. (red at the top)


    i have made the macro copy column a2 down to the end and paste into a seperate tab, then go to b2 and then paste it underneath and so on. thats working and fine


    what i now need is too add 1+23 from A1 the correct number of times to the right of A2 downwards.


    then take 1+23 in B1 and add it to the correct number of lines in B2 downwards


    so , lets assume this has 100 rows


    column A needs to be A2 down to A101 and next to this 1+23 100 times


    if the list was 87 long, id need A2-88 and 1+23 next to it 87 times


    then it goes to the next coulmn and repeats


    thanks


    1+23 1+23 1+23 1+23 1+23
    8000 10000 12000 15000 20000
    20.00 21.19 23.76 28.65 42.48
    20.00 20.00 22.11 25.52 32.35
    20.00 21.19 23.76 28.65 42.48
    20.00 20.00 22.11 25.52 32.35
    20.00 20.00 22.11 25.52 32.35
    20.00 22.39 25.21 30.37 44.25
    28.22 29.76 31.30 47.61 68.58
    20.00 22.97 26.94 32.05 38.95
    31.07 32.96 34.50 52.96 74.98
    31.07 32.96 34.50 52.96 74.98
    20.00 25.15 29.55 35.15 42.15
    31.07 32.96 34.50 52.96 74.98
    20.00 25.15 29.55 35.15 42.15
  • 1 2 3 OUTPUT
    8000 10000 15000 Name Term Group Cat
    aaa a i f aaa 1 8000 a
    bbb b g d bbb 1 8000 b
    ccc c d r ccc 1 8000 c
    ddd d f t ddd 1 8000 d
    eee e h y eee 1 8000 e
    fff f y e fff 1 8000 f
    ggg g f d ggg 1 8000 g
    hhh h d f hhh 1 8000 h
    aaa 2 10000 i
    bbb 2 10000 g
    ccc 2 10000 d
    ddd 2 10000 f
    eee 2 10000 h
    fff 2 10000 y
    ggg 2 10000 f
    hhh 2 10000 d
    aaa 3 15000 f
    bbb 3 15000 d
    ccc 3 15000 r
    ddd 3 15000 t
    eee 3 15000 y
    fff 3 15000 e
    ggg 3 15000 d
    hhh 3 15000 f
  • this example there are 8 lines so it pastes the group and term 8 times with the relevant data


    there could be 3000 lines, so it would need to count to 3000 and paste the group and term 3000 times with the data


    hopefully ive explained better

  • Hello,


    What Alan was suggesting ... is the following :


    He is willing to give a hand ... provided you do attach your Excel file with your next message :)


    i.e. A sample file with enough records to test ... the same structure as your real life file ... no sensitive data ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Thumbs Up" icon, below, in the bottom right corner:)

  • I would do it with Power Query. Here is the Mcode and a sample file based upon what you provided.


  • attached example


    the bit im struggling with is to get the number of lines it has in total and then paste the relevant headers that many times


    hopefully the attachment is better

    thanks