Share via

How do I Stack Multiple Column Pairs on Top of Each Other Using a Formula?

Anonymous
2022-09-23T17:12:17+00:00

Hi, I would really appreciate it if someone could help me with this.

I have an Excel file with two spreadsheets.

Spreadsheet number 1 has three column pairs (six columns essentially).

Spreadsheet number 2 is supposed to only have one column pair (two columns essentially) by combining all three column pairs into one column pair.

I also need spreadsheet number 2 to automatically adjust whenever new items are added in spreadsheet number 1.

I'm having trouble creating spreadsheet number 2 using a formula. Yeah, it really has to be a formula.

To illustrate, spreadsheet number 1 looks like this:

A B C D E F
1 Banana 100 Lettuce 50 Pork 1000
2 Apple 200 Celery 90 Beef 2000
3 Pineapple 120 Parsley 77 Chicken 500
4 Cherry 250 Kale 901 Fish 450
5 Tomato 70 Cucumber 56 Prawn 400

Spreadsheet number 2 should look like this:

A B
1 Banana 100
2 Apple 200
3 Pineapple 120
4 Cherry 250
5 Tomato 70
6 Lettuce 50
7 Celery 90
8 Parsley 77
9 Kale 901
10 Cucumber 56
11 Pork 1000
12 Beef 2000
13 Chicken 500
14 Fish 450
15 Prawn 400

Though I am trying to figure the formula out on my own, I would really appreciate it if a more experienced person could help me out in creating a formula for spreadsheet number 2. I'm on a tight schedule and am hoping asking for help in this forum can expedite the process.

Thank you so much for any assistance you may provide.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2022-09-23T23:48:07+00:00

    Hi,

    in cell I2, enter this formula

    =VSTACK(A2:B6,C2:D6,E2:F6)

    Hope this helps.

    40+ people found this answer helpful.
    0 comments

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-09-23T17:48:03+00:00

    fmba27,

    Your Spreadsheet number 1 with the name Sheet1.

    With formulas:

    In the first column on Sheet2 (A1):

    =INDEX(Sheet1!$A$1:$F$100,TRUNC((2*ROW(A1)-2)/6)+1,MOD(2*ROW(A1)-2,6)+1)

    In the second column on Sheet2 (B1):

    =INDEX(Sheet1!$A$1:$F$100,TRUNC((2*ROW(A1)-2)/6)+1,MOD(2*ROW(A1)-2,6)+2)

    and fill them down as far as you like.

    Jan

    2 people found this answer helpful.
    0 comments
  2. Ashish Mathur 101.8K Reputation points Volunteer Moderator
    2022-09-25T03:39:28+00:00

    You are welcome.

    0 comments
  3. Anonymous
    2022-09-25T02:49:05+00:00

    Thank you! The solution looks so light. I've tried working with arrays, but they just made the excel workbook too slow. I'll give it a try.

    Thank you so much again.

    0 comments
  4. Anonymous
    2022-09-25T02:48:08+00:00

    Thank you so much! I'll try to give this a spin.

    0 comments