+ Reply to Thread
Results 1 to 10 of 10

Create a list of items based on another list

  1. #1
    Registered User
    Join Date
    09-10-2010
    Location
    Lisbon, Portugal
    MS-Off Ver
    Excel 2003
    Posts
    5

    Create a list of items based on another list

    Hi everyone....this is my first post here ....and not my last I believe!

    My question I believe is easily solvable for you cracks of excel.
    I have a worksheet with a list of rows (item#, date, price, etc). In the last column of this list I can put an x for some of the items.
    On another worksheet or sheet, I need to autoamtically have a list of the items in the first list above, that have an X in the last columns.
    I created an example on a worksheet attached just for you to understand.
    I apreciate all the help I can get.
    Thank you so much.
    RG
    Attached Files Attached Files

  2. #2
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Create a list of items based on another list

    Hi In L7

    =IF(COUNTIF($E$7:$E$19,"x")>=ROWS($E$7:E7),INDEX(C$7:C$19,SMALL(IF($E$7:$E$19="x",ROW(INDIRECT("1:"&ROWS($E$7:$E$19)))),ROW(A1))),"")

    Confirm with Ctrl+Shift+Enter
    Люди, питающие благие намерения, как раз и становятся чудовищами.

    Regards, «Born in USSR»
    Vusal M Dadashev

    Baku, Azerbaijan

  3. #3
    Registered User
    Join Date
    09-08-2010
    Location
    Montenegro
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Create a list of items based on another list

    Well, I see, there has been already a solution while I was working, but anyway I will present it; I decided to run a macro to do this; look at the attachment. You must enable macros in order this to work, and there are two worksheets, as you said that you need output in another worksheet; there are two buttons, "Generate list" and "Clear list", I suppose it's obvious what they will do.

    I set macro to check up to 1000 lines; if you have more, just go into code and change it; also, if you don't like names of sheets, "input" and "output", you must change it inside code once you rename them.
    Attached Files Attached Files

  4. #4
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Create a list of items based on another list

    There was no need to loop through 1000 cells. You could just use autofilter option, which seems much faster and better.

    Please Login or Register  to view this content.
    Last edited by contaminated; 09-10-2010 at 07:33 AM.

  5. #5
    Registered User
    Join Date
    09-08-2010
    Location
    Montenegro
    MS-Off Ver
    Excel 2010
    Posts
    79

    Re: Create a list of items based on another list

    Thanks for update, Contaminated, didn't know that trick!

  6. #6
    Registered User
    Join Date
    09-10-2010
    Location
    Lisbon, Portugal
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Create a list of items based on another list

    You guys are incredible!!!! thanks so much.
    Contaminated, for the last comment on the macro, I modified it as follows for my own use, but a sintax error is shown, and I am not able to see why. I wrote:

    Sub Macro1()
    Application.ScreenUpdating = False
    With Sheets(1)
    .Range("A8").AutoFilter Field:=10, Criteria1:="x"
    .Range("A9:Z1000").Copy Range("A9").PasteSpecial xlPasteAll
    .Range("A8").AutoFilter
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
    End Sub

    Where
    my range of data is A9:Z1000
    A8 is my start of ranges
    and column 10 is the criteria (the "x"!)
    Do you see what is wrong?
    Tkx

  7. #7
    Registered User
    Join Date
    09-10-2010
    Location
    Lisbon, Portugal
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Create a list of items based on another list

    Ok, I think it is best to show you the real sheet, without meaningful data.
    In the attachment column 10 (J) is the one that should decide weather to display on sheet(2) if the line is displaied by puttin an ex in column 10.
    I the first formula by contaminated should be the best way to go at it, but I tried and something is not working and I do not know why (keep in mind that for whatever strange reason in Portugal, the comma in the functions has to be changed to a semicolon (
    What I am a doing wrong?????
    thank youuuuuuu!

  8. #8
    Forum Expert contaminated's Avatar
    Join Date
    05-07-2009
    Location
    Baku, Azerbaijan
    MS-Off Ver
    Excel 2013
    Posts
    1,430

    Re: Create a list of items based on another list

    rickyg. Your post breaking several rules of the forum.

    Rule # 2
    2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.
    Rule # 3
    3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Select your code and click the [#] button at the top of the post window (if you are editing an existing post, press Go Advanced to see the [#] button). The result will appear like this in the post window:

    Please Login or Register  to view this content.
    ... and appear like this when posted:

    Code:
    your code here ...
    and here ...
    and here
    You can also type the code tags in manually if you prefer. For more information about these and other tags, click here.
    Please read forum rules.

  9. #9
    Registered User
    Join Date
    09-10-2010
    Location
    Lisbon, Portugal
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Create a list of items based on another list

    forgot the attachment!
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    09-10-2010
    Location
    Lisbon, Portugal
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Create a list of items based on another list

    sorryyyy!!!! I will do so! Sorry again!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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