Solved

Spreading the data in the Excel sheet

Posted on 2011-09-07
2
199 Views
Last Modified: 2012-05-12
Hi,

Pls refer attached sample data sheet. I have about 30 Locations(Have shown only 3) and 4 shifts(A1-A4). I want them laid out in the format shown below. I did that by copy pasting the 3 sample locations shown, but  I didnt want to copy paste for all 30 locations and wanted to know if there was an easier way out.

Thanks,

 sampledata.xlsx
0
Comment
Question by:j1981
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 250 total points
ID: 36499927
You might try copying across the following formulas for cells B11:B13:
=INDEX($A$4:$A$33,1+(COLUMN()-COLUMN($B$11))/4)
=INDEX($C$1:$F$1,MOD(COLUMN()-COLUMN($B$12),4)+1)
=INDEX($C$4:$G$33,1+(COLUMN()-COLUMN($B$13))/4,MOD(COLUMN()-COLUMN($B$13),4)+1)

These formulas are designed to be used wherever you decide to put the table--just update the cell references.
0
 
LVL 10

Assisted Solution

by:SANTABABY
SANTABABY earned 250 total points
ID: 36500378
Attached is another solution attempted using VBA.
You can enter any number of locations and any number of shifts under the specified column and generate the matrix in your way.
I've added another sheet with sample data and code.
sampledata.xlsm
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question