LinkBack URL
About LinkBacks
I am trying to convert a single row of data with repeating columns to multiple output rows
Sample Attached.
I am trying to convert a single row of data with repeating columns to multiple output rows
Sample Attached.
Last edited by jmewebb; 12-05-2012 at 05:38 PM. Reason: added sample workbook
jmewebb, welcome to the forum
To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"
Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
See the attached
It is close to working but I am running into two issues.
1. It is not returning all of the names. It is putting the correct number of projects in the rows just not adding all names. I have six columns of names if that makes any difference.
2. The original list has 1533 rows and I copied the project formula down to the 4000+ row. However, it cuts the projects off at 1521 and will not list the remaining 12 projects.
I appreciate your help!
to fill Project Name write at A2 (array formula)
{=IFERROR(INDEX(Current!$A$1:$A$3;TRUNC(SMALL(IF(LEN(Current!$B$2:$D$3)>1;ROW(Current!$B$2:$D$3)*100+COLUMN(Current!$B$2:$D$3));ROW(1:1))/100));"")}
copy down
to fill Resource write at B2 (array formula)
{=IFERROR(INDEX(Current!$A$1:$D$3;TRUNC(SMALL(IF(LEN(Current!$B$2:$D$3)>1;ROW(Current!$B$2:$D$3)*100+COLUMN(Current!$B$2:$D$3));ROW(1:1))/100);MOD(SMALL(IF(LEN(Current!$B$2:$D$3)>1;ROW(Current!$B$2:$D$3)*100+COLUMN(Current!$B$2:$D$3));ROW(1:1));100));"")}
copy down
The Project Name worked but I couldn't get the Resources to work. The cells remain blank. I have six columns of Resources, would that make a difference?
Thank you!
Last edited by jmewebb; 12-05-2012 at 11:14 PM.
I added an updated sample with the same number of columns as my spreadsheet.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks