• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Rearrange the excel cell

R

rupal1234

Member
I Want to change the location of cell like A3 to B2 (hall no | stall no should be in front of company name.)




Company
A. DAMIANO & COMPANY
Hall No. HALL 9 | Stall No. H9B1
A. EBERLE SYSTEMS PVT. LTD.
Hall No. HALL 14 | Stall No. H14D6
AADI LOCKS PRIVATE LIMITED
Hall No. HALL 16 | Stall No. H16M4
AAKASH POWERTECH PVT. LTD.
Hall No. HALL 10 | Stall No. H10A11
AB POWER SYSTEM SOLUTION
Hall No. HALL 6 | Stall No. H6D1
 

Attachments

64911

1] A2, copied down :

=OFFSET(Sheet1!A$1,ROW(A1)*2-1,0)&""

2] B2, copied down :

=OFFSET(Sheet1!A$1,ROW(A1)*2,0)&""

Or,

Try this single formula solution

In A2 copied right to B2, and all copied down :

=OFFSET(Sheet1!$A$1,ROW(A1)*2-(COLUMN(A1)=1),0)&""

Regards
Bosco
 
Last edited:
Copy this paste in B2 and fill down
=IFERROR(INDEX($A$2:$A$11,IF(ISEVEN(ROW(A2)),MATCH(A2,$A$2:$A$11,0)+1,"")),"") ;):);)
 
Using Power Query

Bring your source to Power Query > Add an index column start with 1, duplicate source and Add an index column start with 0, then merge this two table, add a custom column to indicate odd(1) and even(2) row, filter by odd row (1), delete all the index and filter columns, close and load back to Excel

In future any added values in the source table, you just right-click and refresh the load Power Query table
 

Attachments

Last edited:
bluesky63
Just curious ... is this normal Your Power Query output ... which I got, after opened Your file?
Screenshot 2020-01-12 at 16.45.16.png
bluesky63
... now it looks like 'normal', but it's still in other than Sheet1 - I compared with #1 original writing.
 
Last edited:
You must log in or register to reply here.
Back
Top