+ Reply to Thread
Results 1 to 11 of 11

getting information for a table

  1. #1
    Forum Contributor
    Join Date
    05-03-2020
    Location
    London
    MS-Off Ver
    MICROSOFT 2016
    Posts
    220

    getting information for a table

    Hi all,

    I would like to say first thank you for your help, it is well apricated.

    2 TABS
    RAW DATA
    FILTER

    I have sheet of Raw Data and I want to select different names (the whole row) and present this information, please Filter sheet. At the moment I am coping and pasting the data however has the data grows it takes a long time. I am looking for formula which finds the Name and copy's the information.


    Deepak
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,362

    Re: getting information for a table

    filter

    B5=IF(AND(filter!$A5<>"",filter!B$4<>""),SUMPRODUCT(('raw data'!$A$3:$A$88=filter!$A5)*('raw data'!$B$2:$J$2=filter!B$4)*('raw data'!$B$3:$J$88)),"")

    copy across and down

  3. #3
    Forum Contributor
    Join Date
    05-03-2020
    Location
    London
    MS-Off Ver
    MICROSOFT 2016
    Posts
    220

    Re: getting information for a table

    Hi Caracalla,

    this is exactly what I wanted, thank you.

    However there is an issue, 2 of the columns are not getting the correct information (c7 and c8)


    Your help will be apricated.

    Thank you once again
    Last edited by DEEARO; 12-07-2020 at 11:50 AM.

  4. #4
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,362

    Re: getting information for a table

    In your file xxx2 is present 2 times and then

    the formula does the sum

  5. #5
    Forum Contributor
    Join Date
    05-03-2020
    Location
    London
    MS-Off Ver
    MICROSOFT 2016
    Posts
    220

    Re: getting information for a table

    Hi Caracalla,

    I did not see my mistake in naming the headings,

    This is exactly what I wanted, thank you.

    Thank you once again

  6. #6
    Forum Expert
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    1,362

    Re: getting information for a table

    only for office 365 with double or not headings.

    filter


    B7=FILTER('raw data'!B3:J88,'raw data'!A3:A88=filter!A7,"")
    Last edited by CARACALLA; 12-07-2020 at 07:35 AM.

  7. #7
    Forum Contributor
    Join Date
    05-03-2020
    Location
    London
    MS-Off Ver
    MICROSOFT 2016
    Posts
    220

    Re: getting information for a table

    Hi Caracalla,

    I have some trouble editing the formula to work on my workbook.


    the formula is not dragging across correctly.

    I have Microsoft Office 2016 at work
    Last edited by DEEARO; 12-07-2020 at 12:24 PM.

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

    Re: getting information for a table

    Please try

    =FILTER('Raw Data'!C40:J674,ISNUMBER(MATCH('Raw Data'!$A40:A674,A5:A14,)))
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    05-03-2020
    Location
    London
    MS-Off Ver
    MICROSOFT 2016
    Posts
    220

    Re: getting information for a table

    Hi Bo_Ry

    I had to edit the headings and it went wrong. please have a look

    the below function doesn't work: states function not available
    =FILTER('Raw Data'!C40:J674,ISNUMBER(MATCH('Raw Data'!$A40:A674,A5:A14,)))
    Last edited by DEEARO; 12-07-2020 at 11:51 AM.

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

    Re: getting information for a table

    Try
    =VLOOKUP($A5,'Raw Data'!$A$40:$J$674,MATCH(B$4,'Raw Data'!$A$39:$J$39,))
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    05-03-2020
    Location
    London
    MS-Off Ver
    MICROSOFT 2016
    Posts
    220

    Re: getting information for a table

    Hi Bo_RY,

    thank you for your reply. the formula almost works. the issue I found is that it is not matching the names correctly. I have placed two names not in Raw Data table but the Filter is still finding results.

    your help will be apricated

+ 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. Replies: 0
    Last Post: 04-19-2020, 06:16 AM
  2. Auto add rows in a table when information is input in different table
    By Sammydasalmon in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-15-2019, 05:09 AM
  3. [SOLVED] Macro To copy information in a table (keeping format and nested table intact)
    By meabrams in forum Word Programming / VBA / Macros
    Replies: 6
    Last Post: 03-29-2017, 03:20 AM
  4. Replies: 4
    Last Post: 12-31-2016, 04:06 AM
  5. [SOLVED] Copy and paste formula outside pivot table using information from table
    By batjl9 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-01-2014, 07:10 AM
  6. Heat Mapping by row for a table of information (or a pivot table)
    By jacebailes in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-06-2013, 06:22 PM
  7. Replies: 9
    Last Post: 03-29-2012, 06:11 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1