Forum Thread:

How to Distribute a List of Numbers Across an Equal Number of Rows and Columns?

I have a list of records with item numbers from 5,000 to 10,000. The list could have as few as 1 complete record and as many as 5,000. The list gets populated based on sales of a product. So every time some one purchases an item the transaction fills out the next record in the sequence.

Ex. Starting from 0, someone buys a product, the transaction id goes into the row for item number 5,000. Someone buys another product, the transaction id goes into the row for item number 5,001 and so on.

Based on the number of records that have been filled out completely, I want to distribute the item numbers across a square grid.

If I have 9 sales, I will have 9 rows populated. So I will have transactions associated with item numbers 5,000 to 5,008. I then want to have these 9 numbers (5,000-5,008) added to a 3x3 grid. Basically I want another sheet to automatically populate like this...

5000 5001 5002
5003 5004 5005
5006 5007 5008

If another transaction is added to the list so that we now have 10 item numbers to work with, the grid should expand evenly like this...

5000 5001 5002 5003
5004 5005 5006 5007
5008 5009 ------ ------
------ ------- ------- ------

The grid would not need to re-size for the next 6 transaction.

Right now I have a separate sheet for calculations where it counts the number of products sold (i.e. how many item numbers we are working with in total) and the square root of that number. Then I have a separate field where it rounds the square root up to the nearest whole number.

Any thoughts on how I can accomplish this?

Be the First to Respond

Share Your Thoughts

  • Hot
  • Active