+ Reply to Thread
Results 1 to 7 of 7

Lookup for one value and then copy the whole role.

  1. #1
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    83

    Lookup for one value and then copy the whole role.

    Hi everyone,

    there is a large amount of orders I receive everyday which Needed to be recorded into the master file, in contains of different category of course types.

    Is there a way that could look up the regular courses and copy the whole row down, and whenever new courses are registered, the formula can automatically copy down so that i could have an overview of the Status of the monthly order.

    Please any assistance is greatly appreciated.

    Sheet 1 is master file and sheet 2 is the result i would like to have:


    Sheet 2
    Type Course No. Subject Cost
    regular 4901 Math 9800
    regular 4902 Math 8510
    regular 20181011 Math 7220
    regular 20181015 Math 5930
    regular 1475856 Science 4640
    regular 5565201 Science 3350
    regular 985859 Science 2060
    regular 6521 Science 770
    regular 9852 Science 520
    regular 8522 Literature 651
    regular 20181012 IT 5930
    regular 20181013 Review 4640
    regular 20181014 Science 3350
    regular 20181015 Science 9800
    regular 20181016 Science 8510
    regular 20181017 Science 7220
    regular 20181018 Science 7220
    regular 985 Science 3350
    regular 552 Science 16000

    sheet 1

    Type Course No. Contact person Subject Cost
    regular 4901 Math 9800
    regular 4902 Math 8510
    regular 20181011 Math 7220
    regular 20181015 Math 5930
    regular 1475856 Science 4640
    regular 5565201 Science 3350
    regular 985859 Science 2060
    regular 6521 Science 770
    regular 9852 Science 520
    regular 8522 Literature 651
    regular 20181012 IT 5930
    regular 20181013 Review 4640
    regular 20181014 Science 3350
    regular 20181015 Science 9800
    regular 20181016 Science 8510
    regular 20181017 Science 7220
    regular 20181018 Science 7220
    other 562 PE 5930
    other 956 ART 4640
    regular 985 Science 3350
    regular 552 Science 16000

  2. #2
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Lookup for one value and then copy the whole role.

    See attachment.

    I have included 2 ways of doing this, Power Query, and formulas.

    Power Query:
    All that you have to do in the Power Query version is refresh the green table.

    Formula:
    Formula (in A2 of 'Sheet2 Formula') is this:
    =IFERROR(INDEX('Sheet1 Formula'!A:A,SMALL(IF(Table14[Type]="regular",ROW(Table14[Type])),ROWS($1:1))),"") Ctrl Shift Enter

    I dragged the formula across through D2 then down to row 50.
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Lookup for one value and then copy the whole role.

    Oh wow, that works great !! Thank you very much!

    However I have a Problem since in sheet 2 the Setting is different, the column of contact Person is not required, and I have already more than 2K entries there, i can't just add a column, is there a way to skip columns that i don't want any data pasted?

  4. #4
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Lookup for one value and then copy the whole role.

    I see the "Contact person" header in your sheet1 sample but I don't see any data there. Is the column blank? It's difficult to tell from the layout of your sample.

    Regardless, it should be a simple modification.

    If you wanted to use Power Query, just delete that unwanted column in the Query Editor. If you wanted to use formulas, simply omit the formula for that column.
    The INDEX part of the formula is the only part that has to be changed. If the INDEX is A:A, that's the column that will be brought over.

  5. #5
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Lookup for one value and then copy the whole role.

    Thanks for your prompt Response.

    I did Change the lookup column, but in the course no. column, those belong to material also Pop up... is it possible to eliminate it?

  6. #6
    Forum Expert 63falcondude's Avatar
    Join Date
    08-22-2016
    Location
    USA
    MS-Off Ver
    2016
    Posts
    5,874

    Re: Lookup for one value and then copy the whole role.

    Quote Originally Posted by Sam D View Post
    Thanks for your prompt Response.

    I did Change the lookup column, but in the course no. column, those belong to material also Pop up... is it possible to eliminate it?
    Maybe I am misunderstanding your request. I thought that I answered this in post #4.

    See attachment. This is how to not include a column using Power Query or Formulas.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    05-09-2018
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    83

    Re: Lookup for one value and then copy the whole role.

    I think I have misinterpret the data. Now that I see your sheet. I got it working finally. Thank you very much !!

    I am wondering if the formula can be modified as to populate with "regular" type of courses from more than 1 sheet?
    Since I have another sheet with the same set up which contains regular and non-regular type.

    How should I add up the incremental rows within the 2 sheets?

    Please advice.

    Thanks in adv

+ 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: 12
    Last Post: 05-18-2017, 09:02 AM
  2. Powerpivot role in my company
    By stephme55 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 08-31-2016, 05:36 PM
  3. New role and creation of lots of spreadsheets
    By aange35 in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 08-02-2016, 06:27 AM
  4. Replies: 1
    Last Post: 11-26-2015, 03:41 PM
  5. [SOLVED] Split name and role, get rid of parentheses ex: Last, First (Role)
    By James Keuning in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-02-2014, 01:03 PM
  6. Role Based Security in Excel
    By parag kale in forum Excel General
    Replies: 2
    Last Post: 05-24-2007, 04:26 AM
  7. [SOLVED] how can i make grafs for role call?
    By people in forum Excel General
    Replies: 0
    Last Post: 10-08-2005, 11:05 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