Make lists on new tab from previous tab

  • Thread starter Thread starter JMac
  • Start date Start date
J

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
 
Tab? Worksheets have tabs. Perhaps you're referring to creating a list on a
new worksheet?

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

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

Ken Johnson
Click to expand...


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("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
 
Back
Top