# Create a list of items based on another list

1. ## 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

2. ## 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

3. ## 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.

4. ## 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.``

5. ## Re: Create a list of items based on another list

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

6. ## 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. ## 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. ## 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:
and here ...
and here

9. ## Re: Create a list of items based on another list

forgot the attachment!

10. ## Re: Create a list of items based on another list

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

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

#### 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