excel - How do I convert rows to columns, and repeat the adjacent cells? -

-

i'm sorry if title confusing, visual should help:

here's sheet looks like:

  b c d 1 x y z t 2 q w e r 3 y u o

i need generate separate sheet wherein:

  b c 1 x y t 2 x z t 3 q w r 4 q e r  ...

basically, need middle 2 columns of original sheet transposed , adjacent columns pulled new sheet (as duplicate rows).

i have transpose working correctly, , when pull adjacent columns works too. issue is, can't autofill sheet. when try drag & autofill, instead of autofilling using row 2 original sheet, new sheet autofill using row 3 (which same row # in new sheet).

please let me know if isn't making sense, i'll try explain better! i'm not versed in google spreadsheets scripts - i've tried before seem rather cumbersome. i'm happy try well.

i'm not sure transpose operation come play little conditional maths , index function¹ should suffice.

=index($a$1:$d$3,(row(1:1)-1)/2+1,if(column(a:a)=3,4,if(and(iseven(row(1:1)),column(a:a)=2),3,column(a:a))))

index_repeat_gs

the documentation i've linked excel syntax identical these purposes.

addendum more columns

by adjusting condition offset after third column, more columns can readily accounted for.

=index($a$1:$j$3,(row(1:1)-1)/2+1,if(column(a:a)>=3,column(a:a)+1,if(and(iseven(row(1:1)),column(a:a)=2),3,column(a:a))))

index_offset_repeat

the above collects 10 columns source matrix should auto-adjusting pretty number of columns.

¹ the index function accepts parameters both row number , column number. although typically 1 of these used, there no restriction against supplying both against 2d range of cells.

  • put row_number calculation,
          =(row(1:1)-1)/2+1
     into cell , fill down. receive 1, 1, 2, 2, 3, 3, etc. supplies repeating row number a1:d3 range.

  • put column_number calculation,
          =if(column(a:a)=3,4,if(and(iseven(row(1:1)),column(a:a)=2),3,column(a:a)))
     into cell , fill right , down. receive 1, 2, 4 first row , 1, 3, 4 second. pattern repeats subsequent rows , supplies offset column numbers a1:d3 range.


Comments

Post a Comment

Popular posts from this blog

sequelize.js - Sequelize group by with association includes id -

-
so when requesting group sequelize follows: return models.workingcalendar .findall({ attributes: [ 'workingcalendar.periodid', 'workingcalendar.date', 'period.name' ], include: [ { model: models.period, attributes: [] } ], where: { getsudoid: currentgetsudo.id, unitplantid: unitplantid }, group: ['workingcalendar.periodid', 'workingcalendar.date', 'period.name'], }); sequelize run query: select [workingcalendar].[id], [workingcalendar].[periodid], [workingcalendar].[date], [period].[name] [workingcalendars] [workingcalendar] left outer join [periods] [period] on [wor...
Read more

delphi - Take screenshot in webcam using VFrames in Console Application -

-
i'm doing program take picture of webcam using delphi xe2 , vframes achieve this, problem have figured out, in graphic application works fine, when use unit in console application, returns me error saying first chance exception @ $76b6b727. exception class eaccessviolation message 'access violation @ address 004a271b in module 'console.exe'. read of address 00000260'. process console.exe (3676) my unit : unit webcam; interface uses sysutils, windows, vcl.imaging.jpeg, vcl.graphics, vsample, vframes, classes; type twebcam = class private procedure newvideoframeevent(sender: tobject; width, height: integer; dataptr: pointer); public constructor create; destructor destroy; override; procedure capture_webcam(take_name: string); end; var web_image: tvideoimage; name_screen: string; implementation constructor twebcam.create; begin inherited create; end; destructor twebcam.destroy; begin inherited destroy; end; ...
Read more

php - Few issues in voting -

-
<?php include_once 'dbconnect.php'; include_once 'index_header.php'; $article_id = $_get['id']; $counter = 0; // select article db $sql = $con->query("select * posts post_id = '$article_id'"); if ($sql){ while($row = mysqli_fetch_assoc($sql)) { $select_title = $row['title']; $select_article = $row['article']; }} if(isset($_post['downvote'])){ $counter = $counter - 1; } if(isset($_post['upvote'])){ $counter = $counter + 1; if($counter<=1){ $insert_query = $con->query("insert votes(vote_count,post_id) values(1,'$article_id')"); } } $selet_votes = $con->query("select sum(vote_count) vote_count_sum votes post_id= '$article_id'"); if ($selet_votes){ $row_votes = mysqli_fetch_assoc($selet_votes); $votes = $r...
Read more