Divided set of data by choice in Sequence

ivanlost

New Member
Joined
Feb 22, 2023
Messages
5
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi, trying to find formula to divide data set in one column to 100 rows in a way that it follows sequence. Each row to divide by number of values of my choice and give me that number of columns per row. Here is presented only 3 columns. Numbers on left are my choice by how many columns by row I want to divide data set. Please check example photo.
 

Attachments

  • IMG_20230314_020912_edit_508380079417216.jpg
    IMG_20230314_020912_edit_508380079417216.jpg
    194.8 KB · Views: 10
A 365 only option:
Book1
ABCDEFGHIJKLMN
1Count
20 
31a01
42a02a03
53a04a05a06
64a07a08a09a10
75a11a12a13a14a15
86a16a17a18a19a20a21
97a22a23a24a25a26a27a28
108a29a30a31a32a33a34a35a36
119a37a38a39a40a41a42a43a44a45
1210a46a47a48a49a50a51a52a53a54a55
1311a56a57a58a59a60a61a62a63a64a65a66
1412a67a68a69a70a71a72a73a74a75a76a77a78
1513a79a80a81a82a83a84a85a86a87a88a89a90a91
1614a92a93a94a95a96a97a98a99a100
Sheet2
Cell Formulas
RangeFormula
B2:B3,B16:J16,B15:N15,B14:M14,B13:L13,B12:K12,B11:J11,B10:I10,B9:H9,B8:G8,B7:F7,B6:E6,B5:D5,B4:C4B2=IFERROR(TOROW(TAKE(DROP(Sheet1!$A$2:$A$101,SUM($A$1:A1)),Sheet2!A2)),"")
 
Upvote 0

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi, I created the newer version of the table...

The new formula used in the new table:
=IF((COLUMN(B$1:CW$1)<$A2+2)*(SUM($A$1:$A1)+COLUMN(B$1:CW$1)<102),"a"&SUM($A$1:$A1)+COLUMN(B$1:CW$1)-1,"")

DevideNewer.xlsx

DevideNewer.png
 
Upvote 0
Hi, one more modification...

The newest formula:
=IF((COLUMN(B2:CW2)<$A2+2)*(SUM($A$1:$A1)+COLUMN(B2:CW2)<$H$1+2),$D$1&SUM($A$1:$A1)+COLUMN(B2:CW2)-1,"")

DevideNewer2.xlsx

DevideNewer2.png
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top