Solved

Spreading the data in the Excel sheet

Posted on 2011-09-07
2
206 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

726 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