Google Spreadsheet Post #1168
Yogi Anand, D.Eng, P.E. ANAND Enterprises LLC -- Rochester Hills MI www.energyefficientbuild.com. May 04, 2013
user Fun SpiritMan (http://productforums.google.com/forum/?zx=crpyw0e3sdva#!category-topic/docs/spreadsheets/5ZbZBFziPDQ)
How to copy data from column A to columns B and C, effectively using half the number of rows
I have data in column A that I want to duplicate in columns B and C as follows:
A B C
1) 6 6 5
2) 5 4 3
3) 4 2 1
4) 3 7 8
5) 2 9 13
6) 1 14
7) 7
8) 8
9) 9
10) 13
11) 14
---
Update: The ideal solution would allow me to use up to 4 columns (B, C, D, E) to represent the data in original column A. Here is a second
view of the data and results using the max of 4 columns:
A B C D E
1) 6 6 5 4 3
2) 5 2 1 7 8
3) 4 9 13 14
4) 3
5) 2
6) 1
7) 7
8) 8
9) 9
10) 13
11) 14
Any help would be appreciated. Thanx.
----------------------------------------------------------------------------------------------------------------
following is a solution to the problwm
Yogi, Thanx for your attention to this coding problem. In the several days since my first posting, I had thought to use a series of TRANSPOSE of Column A rows (hard-coded for the number of columns B, C or B,C,D, E I wanted) across the columns. Somehow I felt that a more "elegant" solution should be possible. Your solution looks elegant to me. Let me take some time to dissect it for comprehension. As I played with my idea of using TRANSPOSE (which should work with brute force) I was starting to think I needed to employ the ROW function with a MOD... but I started to get a headache. I'm not surprised that your solution is using both of those functions. At least I was on the right track. Thanx, especially for this boost in my confidence. Len
ReplyDeleteHi Len:
ReplyDeleteGreat ... Now Let Us Keep Googling.
By the way, my formula uses the INDIRECT and MOD functions ... and I have also provided the solution to a bit more generalized problem in that you can have a variable Step size for any of the groups.
Make It A Great One
Cheers!
Yogi
Cloud Computing -- Google Docs Way
yogi--anand-consulting.blogspot.com
Yogi,
ReplyDeleteThanx for mentioning that Step Size. I was misunderstanding it at first to be the 4th "step" to setting up the sheet. I appreciate that you have generalized the solution.
I will attempt to re-create your solution as soon as possible. So since I still have the issue with accessing Google Docs via my laptop main computer, as we discussed awhile ago, it is hard to use Google Docs quickly and effectively. Currently, I have a CR-48 Chromebook which allows me to access Google Docs, but which is hard to control with a mouse or the keyboard. Thanx again.
Len