+ Reply to Thread
Results 1 to 10 of 10

macro needed to extract specific columns out of multiple columns with their row data

  1. 01-06-2014, 01:27 AM #1
    genetist
    genetist is offline
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    176

    macro needed to extract specific columns out of multiple columns with their row data

    Hi to all,
    I have data like this,
    name age Eye color education country village
    pietro 22 red bsc Italy viterbo
    luigi 25 blue msc germany frosinone
    maurizio 34 white msc spain rome
    andrea 35 pink bsc portugal villalante
    anna 65 green msc estonia bagnaia
    giulia 23 black msc finland portis
    federica 27 violet bsc sweden fortna
    Now i want to extract only some columns out of all for example education and village. How can i extract all the columns at a time instead of one by one.

    Expected results
    name education village
    pietro bsc viterbo
    luigi msc frosinone
    maurizio msc rome
    andrea bsc villalante
    anna msc bagnaia
    giulia msc portis
    federica bsc fortna
    in my file i have like more than 10000 columns here i presented only example data. any help would be highly appreciated.
    Thanks in advance
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 01-06-2014, 01:33 AM #2
    hemesh
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: macro needed to extract specific columns out of multiple columns with their row data

    Hello genetist ! Your requirement can be completed with formula also is it ok !
    -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

    WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST

    More we learn about excel, more it shows us, how less we know about it.

    for chemistry
    https://www.youtube.com/c/chemistrybyshivaansh
    Reply With QuoteRegister To Reply
  3. 01-06-2014, 01:38 AM #3
    genetist
    genetist is offline
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    176

    Re: macro needed to extract specific columns out of multiple columns with their row data

    Hi Hemesh,
    Thank you very much for your reply and spending your time to solve my problem, Yes formula will be o.k but i have to extract more or less 300 columns spreaded randomely in 10000 columns. I do not know how formula can work in this situation. I want give all the columns names to extract and VBA or should extract only those provided columns data along with their rows data.
    Thanking you very much
    Reply With QuoteRegister To Reply
  4. 01-06-2014, 01:41 AM #4
    hemesh
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: macro needed to extract specific columns out of multiple columns with their row data

    can you please upload a sample book of your actual data .
    Reply With QuoteRegister To Reply
  5. 01-06-2014, 02:14 AM #5
    genetist
    genetist is offline
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    176

    Re: macro needed to extract specific columns out of multiple columns with their row data

    Dear Hemesh,
    Please have a look on my attached file.
    Thanks
    Reply With QuoteRegister To Reply
  6. 01-06-2014, 02:26 AM #6
    hemesh
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: macro needed to extract specific columns out of multiple columns with their row data

    select sheet 2 a2 and copy paste below
    =IFERROR(INDEX(Sheet1!$A$3:$F$10000,SMALL(IF(Sheet1!$A$3:$A$10000<>"",ROW(Sheet1!$A$3:$A$10000)-ROW($A$3)+1),ROWS($A$1:A1)),1),"")
    then hold control and shift together now hit enter

    now in cell b2 copy paste
    =IFERROR(INDEX(Sheet1!$A$3:$F$10000,SMALL(IF(Sheet1!$A$3:$A$10000<>"",ROW(Sheet1!$A$3:$A$10000)-ROW($A$3)+1),ROWS($A$1:A1)),4),"")
    then hold control and shift together now hit enter

    Now in cell c2
    =IFERROR(INDEX(Sheet1!$A$3:$F$10000,SMALL(IF(Sheet1!$A$3:$A$10000<>"",ROW(Sheet1!$A$3:$A$10000)-ROW($A$3)+1),ROWS($A$1:A1)),6),"")
    then hold control and shift together now hit enter

    drag all formulas down
    Reply With QuoteRegister To Reply
  7. 01-06-2014, 02:37 AM #7
    genetist
    genetist is offline
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    176

    Re: macro needed to extract specific columns out of multiple columns with their row data

    Hi hemesh,
    I am sorry for some reasons after using i am getting out put like this
    pietro pietro pietro
    luigi luigi luigi
    maurizio maurizio maurizio
    andrea andrea andrea
    anna anna anna
    giulia giulia giulia
    federica federica federica

    and it is not in my expected way
    name education village
    pietro bsc viterbo
    luigi msc frosinone
    maurizio msc rome
    andrea bsc villalante
    anna msc bagnaia
    giulia msc portis
    federica bsc fortna
    Last edited by genetist; 01-06-2014 at 02:43 AM.
    Reply With QuoteRegister To Reply
  8. 01-06-2014, 02:45 AM #8
    hemesh
    hemesh is offline
    Forum Expert
    Join Date
    02-19-2013
    Location
    India
    MS-Off Ver
    07/16
    Posts
    2,386

    Re: macro needed to extract specific columns out of multiple columns with their row data

    there is only one change in formula change 1 to 4 in B2 and 1 to 6 in C2
    =IFERROR(INDEX(Sheet1!$A$3:$F$10000,SMALL(IF(Sheet1!$A$3:$A$10000<>"",ROW(Sheet1!$A$3:$A$10000)-ROW($A$3)+1),ROWS($A$1:A1)),1),"")
    Reply With QuoteRegister To Reply
  9. 01-06-2014, 05:01 PM #9
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: macro needed to extract specific columns out of multiple columns with their row data

    Is this what you wanted?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 01-07-2014, 12:49 AM #10
    genetist
    genetist is offline
    Forum Contributor
    Join Date
    04-09-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    176

    Re: macro needed to extract specific columns out of multiple columns with their row data

    Dear AB33,
    Thank you very much for your and it is working as i am expected, you saved my lot of time.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to combine multiple columns keeping few columns specific
    By lramesh in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-16-2013, 12:44 PM
  2. [SOLVED] Extract specific data from columns and creating seperate sheets for each one
    By Tyso in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-28-2013, 06:03 AM
  3. Macro to extract specific Columns
    By bholabhala in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-13-2010, 07:45 PM
  4. code to extract specific data into specific columns
    By lmohaxx in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-15-2008, 01:52 AM
  5. macro help = extract content of specific columns accross a row
    By [email protected] in forum Excel General
    Replies: 1
    Last Post: 08-03-2006, 07:55 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1