+ Reply to Thread
Results 1 to 9 of 9

Extracting into table help

  1. #1
    Forum Contributor
    Join Date
    01-21-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    116

    Extracting into table help

    Greetings,

    Please could I ask for some help in extracting some data on one sheet and placing into a table on another.

    I have attached a spreadsheet which shows what I am trying to accomplish.


    When initials entered in a cell, I require excel to recall data from "Data" Sheet under the initials of "AAA" as shown in this example. Excel then lists in the table all the fault numbers and descriptions (fault 1 to 10) on each column before moving to the next row. That have "AAA" in the initials row.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    08-14-2018
    Location
    Hanoi, Vietnam
    MS-Off Ver
    2016
    Posts
    118

    Re: Extracting into table help

    I have an idea but i do not know if you are happy with some helper cols.

  3. #3
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extracting into table help

    This is a very long formula

    F7 drag across and down

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Attached Files Attached Files

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Extracting into table help

    Alternative array formula** in F7 and then copied down and right as required:

    =IFERROR(INDIRECT("Data!"&TEXT(SMALL(IF(Data!$D$2:$D$100=$C$3,IF(Data!$E$2:$X$100<>"",IF(MOD(COLUMN(Data!$E$1:$X$1)-MIN(COLUMN(Data!$E$1:$X$1)),2)=COLUMNS($A7:A7)-1,10^5*ROW(Data!$E$2:$X$100)+COLUMN(Data!$E$2:$X$100)))),ROWS(A$7:A7)),"R0C00000"),0),"")

    Regards


    **Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extracting into table help

    Shorter one,
    F7
    =IFERROR(INDIRECT("Data!"&TEXT(AGGREGATE(15,6,ROW(Data!$E$2:$X$10)*10^3+COLUMN(Data!$E$2:$X$10)/(Data!$D$2:$D$10=$C$3)/(Data!$E$2:$X$10<>""),ROWS(F$7:F7)*2-2+COLUMNS($F7:F7)),"R0C000"),),"")
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    01-21-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Extracting into table help

    Thank you, this also works perfectly. Array or not to Array. That will be the question!. Thank you kind people.

  7. #7
    Forum Contributor
    Join Date
    01-21-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Extracting into table help

    Dear XOR LX,

    Sorry to trouble you again or other users. I have tried without success to add an additional column to lookup the month column C in Data tab. SO that I know which month the fault was raised.

    I have attached the sheet, the section highlighted in green is the area i need help.

    Thank you.
    Attached Files Attached Files

  8. #8
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Extracting into table help

    Please try at E7
    =IFERROR(INDEX(Data!C:C,AGGREGATE(15,6,ROW(Data!$E$2:$X$10)/(Data!$D$2:$D$10=$C$3)/(Data!$E$2:$X$10<>""),ROWS(E$7:E7)*2)),"")

  9. #9
    Forum Contributor
    Join Date
    01-21-2015
    Location
    London
    MS-Off Ver
    Office 365
    Posts
    116

    Re: Extracting into table help

    Thank you, Perfect. You have made my day

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Extracting text from table and inserting it in another table/graph
    By Thuns in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-25-2018, 06:59 AM
  2. Comparing cell value to a table then extracting data from second table column
    By JaredMcOffice in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-05-2017, 01:42 AM
  3. Replies: 5
    Last Post: 01-28-2016, 01:43 PM
  4. [SOLVED] Extracting data from a column in a table to another table/sheet
    By hoowill12 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-02-2014, 05:47 PM
  5. Extracting data from table for related table
    By ikavdiya in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-05-2013, 03:20 PM
  6. Extracting data from a table based on a common entries with another table
    By shannoncox in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-31-2012, 04:45 PM
  7. [SOLVED] Extracting values from a table
    By Rob Cherry in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-02-2006, 05:30 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