I'm sure this is somewhere out on the net, but I can't find it.

How do I create a list on a new tab, from a list of values that were
imported into the workbook?

Tab 1
Name,acct#,group
John Doe,6676,rc10
Jane Smith,6782,rc10
John Hopkins,2342,tj21
Jane Doe, 6980,ut41

What I want to do is separate the list by group to their respective
tabs.

Tab rc10
Tab tj21
Tab ut41

Thanks

Tab? Worksheets have tabs. Perhaps you're referring to creating a list on a
new worksheet?

Tyro

"JMac" <mcdowdjr@gmail.com> wrote in message
news:4e0e249c-25d8-48f9-a48c-245a339ceb12@z17g2000hsg.googlegroups.com...
> I'm sure this is somewhere out on the net, but I can't find it.
>
> How do I create a list on a new tab, from a list of values that were
> imported into the workbook?
>
> Tab 1
> Name,acct#,group
> John Doe,6676,rc10
> Jane Smith,6782,rc10
> John Hopkins,2342,tj21
> Jane Doe, 6980,ut41
>
> What I want to do is separate the list by group to their respective
> tabs.
>
> Tab rc10
> Tab tj21
> Tab ut41
>
> Thanks

Here is a method with Pivot Table
or Advanced Filter. VBA addition needed.
http://www.freefilehosting.net/download/3ai95

On Jan 19, 4:42*pm, Herbert Seidenberg <herbds7-ms...@yahoo.com>
wrote:
> Here is a method with Pivot Table
> or Advanced Filter. VBA addition needed.http://www.freefilehosting.net/download/3ai95

HS-

Thanks for the information that is great. However, I would like to
keep it automated. Where the user will not have to do anything. Is it
possible to use a formula that will lookup the clinic values and list
them on the seperate tab in the workbook?

Hi JMac,

This seems to work, but you might not like it because of step 1!

1. Shift your Tab 1 data across 1 column by selecting A1 then
inserting a column (Insert|Columns). This enables the use of the
VLOOKUP function on your other sheets. This helper column can of
course be hidden latter.

2. paste this formula in A2...

=D2&COUNTIF($D$2:$D$1001,D2)-COUNTIF(D2:D$1001,D2)+1

increase the 1001 if your Tab 1 table has more than 1000 entries.

3. Fill the formula down as far as needed. This formula simply appends
each value in column D (rc10, tj21 and ut41 in you sample data) with a
number that corresponds to its position in the table eg the 50th tj21
will result in tj2150.

4. With the headings, Name and Acct# in row 1 on all the other sheets,
and with their sheet names being rc10, tj21, ut41 etc, paste the
following formula into A2 on each of those sheets...

=VLOOKUP(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))
+1,255)&ROW(A1),Sheet1!$A$2:$C$1001,COLUMN(B1),FAL SE)

5. Fill the formula across to B2 then fill down for as many rows as
you filled down on sheet1

6. If you don't like the 1s that appear in column A on Sheet1 below
your existing data then use...

=IF(D2="","",D2&COUNTIF($D$2:$D$1001,D2)-COUNTIF(D2:D$1001,D2)+1)

and if you don't like the NA#s on the other sheets then use...

=IF(ISNA(VLOOKUP(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))
+1,255)&ROW(A1),Sheet1!$A$2:$C
$1001,COLUMN(B1),FALSE)),"",VLOOKUP(MID(CELL("file name",A1),FIND("]",CELL("filename",A1))
+1,255)&ROW(A1),Sheet1!$A$2:$C$1001,COLUMN(B1),FAL SE))

Ken Johnson

On Jan 21, 8:00*pm, Ken Johnson <KenCJohn...@gmail.com> wrote:
> Hi JMac,
>
> This seems to work, but you might not like it because of step 1!
>
> 1. Shift your Tab 1 data across 1 column by selecting A1 then
> inserting a column (Insert|Columns). This enables the use of the
> VLOOKUP function on your other sheets. This helper column can of
> course be hidden latter.
>
> 2. paste this formula in A2...
>
> =D2&COUNTIF($D$2:$D$1001,D2)-COUNTIF(D2:D$1001,D2)+1
>
> increase the 1001 if your Tab 1 table has more than 1000 entries.
>
> 3. Fill the formula down as far as needed. This formula simply appends
> each value in column D (rc10, tj21 and ut41 in you sample data) with a
> number that corresponds to its position in the table eg the 50th tj21
> will result in tj2150.
>
> 4. With the headings, Name and Acct# in row 1 on all the other sheets,
> and with their sheet names being rc10, tj21, ut41 etc, paste the
> following formula into A2 on each of those sheets...
>
> =VLOOKUP(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))
> +1,255)&ROW(A1),Sheet1!$A$2:$C$1001,COLUMN(B1),FAL SE)
>
> 5. Fill the formula across to B2 then fill down for as many rows as
> you filled down on sheet1
>
> 6. If you don't like the 1s that appear in column A on Sheet1 below
> your existing data then use...
>
> =IF(D2="","",D2&COUNTIF($D$2:$D$1001,D2)-COUNTIF(D2:D$1001,D2)+1)
>
> and if you don't like the NA#s on the other sheets then use...
>
> =IF(ISNA(VLOOKUP(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))
> +1,255)&ROW(A1),Sheet1!$A$2:$C
> $1001,COLUMN(B1),FALSE)),"",VLOOKUP(MID(CELL("file name",A1),FIND("]",CELL("*filename",A1))
> +1,255)&ROW(A1),Sheet1!$A$2:$C$1001,COLUMN(B1),FAL SE))
>
> Ken Johnson


Ken-

I have another dilemma, how would i sort for a group of smc1 and then
smc. The groups were not made by me, I'm trying to use data I was
given. The append works for the smc1 group, but the smc group will
also list the smc1 members..any ideas?

Thanks,
Josh

Hi Josh,
For the short but untidy version...

=D2&":"&COUNTIF($D$2:$D$1001,D2)-COUNTIF(D2:D$1001,D2)+1
and
=VLOOKUP(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))
+1,255)&":"&ROW(A1),Sheet1!$A$2:$C$1001,COLUMN(B1) ,FALSE)

For the tidy version...

=IF(D2="","",D2&":"&COUNTIF($D$2:$D$1001,D2)-COUNTIF(D2:D$1001,D2)+1)
and

=IF(ISNA(VLOOKUP(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))
+1,255)&":"&ROW(A1),Sheet1!$A$2:$C
$1001,COLUMN(B1),FALSE)),"",VLOOKUP(MID(CELL("file name",A1),FIND("]",CELL("filename",A1))
+1,255)&":"&ROW(A1),Sheet1!$A$2:$C$1001,COLUMN(B1) ,FALSE))

solves that problem by separating number in group from count number
with a ":".

Ken Johnson