Make lists on new tab from previous tab

[JMac]

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

 

[Tyro]

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

Tyro

 

[Herbert Seidenberg]

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

 

[JMac]

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?

 

[Ken Johnson]

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$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),FALSE)

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$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("filename",A1),FIND("]",CELL("filename",A1))
+1,255)&ROW(A1),Sheet1!$A$2:$C$1001,COLUMN(B1),FALSE))

Ken Johnson

 

[JMac]

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$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),FALSE)

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$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("filename",A1),FIND("]",CELL("­filename",A1))
+1,255)&ROW(A1),Sheet1!$A$2:$C$1001,COLUMN(B1),FALSE))

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

 

[Ken Johnson]

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

=D2&":"&COUNTIF($D$2:$D$1001,D2)-COUNTIF(D2$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$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("filename",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