Results 1 to 3 of 3

2 columns to 1

Threaded View

dbridges99 2 columns to 1 04-25-2015, 02:37 PM
mrice Re: 2 columns to 1 04-25-2015, 02:48 PM
newdoverman Re: 2 columns to 1 04-25-2015, 04:23 PM
  1. #3
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: 2 columns to 1

    If your data starts in row 1
    Formula: copy to clipboard
    =IF(ROW()>COUNT(A:A)+COUNT(B:B),"",IF(MOD(ROW(),2)=1,INDIRECT("A"&ROW()-INT(ROW()/2)),INDIRECT("B"&ROW()-INT(ROW()/2))))


    If your data starts in row 2
    Formula: copy to clipboard
    =IF(MOD(ROW(),2)=0,INDIRECT("A"&ROW()+1-INT((ROW()+1)/2)),INDIRECT("B"&ROW()+1-INT((ROW()+1)/2)))


    If your data starts in row 3

    =IF(MOD(ROW(),2)=1,INDIRECT("A"&ROW()+2-INT((ROW()+2)/2)),INDIRECT("B"&ROW()+2-INT((ROW()+2)/2)))

    Modified and eliminates 0s at end of data. I have used full column references that should be shortened to be suitable for your data:
    Start in Row 1:
    Formula: copy to clipboard
    =IF(ROW()>COUNT(A:B),"",IF(MOD(ROW(),2)=1,INDIRECT("A"&ROW()-INT(ROW()/2)),INDIRECT("B"&ROW()-INT(ROW()/2))))


    Start in Row 2:
    Formula: copy to clipboard
    =IF(ROW()-1>COUNT(A:B),"",IF(MOD(ROW(),2)=0,INDIRECT("A"&ROW()-INT((ROW()-1)/2)),INDIRECT("B"&ROW()-INT((ROW()-1)/2))))


    Start in Row 3:
    Formula: copy to clipboard
    =IF(ROW()-2>COUNT(A:B),"",IF(MOD(ROW(),2)=1,INDIRECT("A"&ROW()-INT((ROW()-2)/2)),INDIRECT("B"&ROW()-INT((ROW()-2)/2))))


    Start in Row 4:
    Formula: copy to clipboard
    =IF(ROW()-3>COUNT(A:B),"",IF(MOD(ROW(),2)=0,INDIRECT("A"&ROW()-INT((ROW()-3)/2)),INDIRECT("B"&ROW()-INT((ROW()-3)/2))))
    Last edited by newdoverman; 04-26-2015 at 11:18 AM. Reason: Correct error in formulae
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find Duplicates across 2 pairs of columns (2 columns against 2 columns)
    By erjfly2013 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-09-2015, 06:52 PM
  2. Macro to Copy from 2 Columns, Paste Value in the next 2 Columns, Then clear 1st Columns
    By MHALTTUNEN in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-22-2014, 03:34 PM
  3. Combining multiple rows from 2 columns into sorted columns depending on 1st columns value
    By Dexamphetamine in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-11-2013, 10:00 AM
  4. MAcro to - copy, transpose, text to columns, remove column A, combine all columns in 1
    By galvinpaddy in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 02-07-2013, 08:48 AM
  5. Replies: 6
    Last Post: 12-26-2012, 01:43 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1