After "Text to Columns" Selection
 

Hi,

I have a column of data from MSProject which has comma seperated values in
one column. I have used the "Text to Columns" feature but now I need to do a
lookup of the 3 columns and select all instances of a particular value.

i.e. the original column data reads

x Builder,Carters Despatch,Carters Delivery
Carters Despatch
Jack Halsey,Hoults Doors,Carters Despatch

Once seperated I need to display all instances of "Carters Despatch"
regardless of the column they are in.

If there is a better approach whoopee, otherwise can someone please let me
know how I so the selection.

Any help appeciated - thanks.

After "Text to Columns" Selection
 

If you're doing this as kind of a one shot deal, then maybe you could do this:

Insert a header row (row 1) if you don't have one already.

Insert a new column (say column A).

Put "Carters Despatch" (or Carter's Dispatch????) in A1
then put this in A2:
=countif(b2:x2,$a$1)

This will give you a count of how many times the text in A1 appears in B2:X2
(change X to your last column--D if your data really is only 3 columns after
Data|Text to columns).

Then apply Data|filter|Autofilter to column A
Filter to show anything greater than 0.

If you need to search for a different value, just show all the data
(data|Filter|show all) and then type in the new value in A1 and filter to show
greater than 0.


Janet BN wrote:

Hi,

I have a column of data from MSProject which has comma seperated values in
one column. I have used the "Text to Columns" feature but now I need to do a
lookup of the 3 columns and select all instances of a particular value.

i.e. the original column data reads

x Builder,Carters Despatch,Carters Delivery
Carters Despatch
Jack Halsey,Hoults Doors,Carters Despatch

Once seperated I need to display all instances of "Carters Despatch"
regardless of the column they are in.

If there is a better approach whoopee, otherwise can someone please let me
know how I so the selection.

Any help appeciated - thanks.

--

Dave Peterson

After "Text to Columns" Selection
 

Thanks for this Dave - this will work in part. However, once I have
determined how to find the resource, I need to be able to use the data in a
pivot table.

p.s The resources name really is Carters Despatch

"Dave Peterson" wrote:

If you're doing this as kind of a one shot deal, then maybe you could do this:

Insert a header row (row 1) if you don't have one already.

Insert a new column (say column A).

Put "Carters Despatch" (or Carter's Dispatch????) in A1
then put this in A2:
=countif(b2:x2,$a$1)

This will give you a count of how many times the text in A1 appears in B2:X2
(change X to your last column--D if your data really is only 3 columns after
Data|Text to columns).

Then apply Data|filter|Autofilter to column A
Filter to show anything greater than 0.

If you need to search for a different value, just show all the data
(data|Filter|show all) and then type in the new value in A1 and filter to show
greater than 0.


Janet BN wrote:

Hi,

I have a column of data from MSProject which has comma seperated values in
one column. I have used the "Text to Columns" feature but now I need to do a
lookup of the 3 columns and select all instances of a particular value.

i.e. the original column data reads

x Builder,Carters Despatch,Carters Delivery
Carters Despatch
Jack Halsey,Hoults Doors,Carters Despatch

Once seperated I need to display all instances of "Carters Despatch"
regardless of the column they are in.

If there is a better approach whoopee, otherwise can someone please let me
know how I so the selection.

Any help appeciated - thanks.

--

Dave Peterson

After "Text to Columns" Selection
 

I don't know what you're doing, but that's never stopped me!

How about putting each resource in a row by itself. If you have to group each
row's worth of resources, maybe you can add an id for each item in that group

So this:
x Builder,Carters Despatch,Carters Delivery
would look like:

1 x Builder
1 Carters Despatch
1 Carters Delivery
2 ...
2 ...
2 ...
3 ...
3 ...
3 ...

But I have no idea how this would fit into a pivottable.



Janet BN wrote:

Thanks for this Dave - this will work in part. However, once I have
determined how to find the resource, I need to be able to use the data in a
pivot table.

p.s The resources name really is Carters Despatch

"Dave Peterson" wrote:

If you're doing this as kind of a one shot deal, then maybe you could do this:

Insert a header row (row 1) if you don't have one already.

Insert a new column (say column A).

Put "Carters Despatch" (or Carter's Dispatch????) in A1
then put this in A2:
=countif(b2:x2,$a$1)

This will give you a count of how many times the text in A1 appears in B2:X2
(change X to your last column--D if your data really is only 3 columns after
Data|Text to columns).

Then apply Data|filter|Autofilter to column A
Filter to show anything greater than 0.

If you need to search for a different value, just show all the data
(data|Filter|show all) and then type in the new value in A1 and filter to show
greater than 0.


Janet BN wrote:

Hi,

I have a column of data from MSProject which has comma seperated values in
one column. I have used the "Text to Columns" feature but now I need to do a
lookup of the 3 columns and select all instances of a particular value.

i.e. the original column data reads

x Builder,Carters Despatch,Carters Delivery
Carters Despatch
Jack Halsey,Hoults Doors,Carters Despatch

Once seperated I need to display all instances of "Carters Despatch"
regardless of the column they are in.

If there is a better approach whoopee, otherwise can someone please let me
know how I so the selection.

Any help appeciated - thanks.

--

Dave Peterson


--

Dave Peterson

After "Text to Columns" Selection
 

Thanks Dave, but I don't think that would work as the data is generated daily
out of MSP and the table in excel already has 26k lines - I'm trying to make
it as simple as possible for other people to generate the reports.

I'll keep thinking.

"Dave Peterson" wrote:

I don't know what you're doing, but that's never stopped me!

How about putting each resource in a row by itself. If you have to group each
row's worth of resources, maybe you can add an id for each item in that group

So this:
x Builder,Carters Despatch,Carters Delivery
would look like:

1 x Builder
1 Carters Despatch
1 Carters Delivery
2 ...
2 ...
2 ...
3 ...
3 ...
3 ...

But I have no idea how this would fit into a pivottable.



Janet BN wrote:

Thanks for this Dave - this will work in part. However, once I have
determined how to find the resource, I need to be able to use the data in a
pivot table.

p.s The resources name really is Carters Despatch

"Dave Peterson" wrote:

If you're doing this as kind of a one shot deal, then maybe you could do this:

Insert a header row (row 1) if you don't have one already.

Insert a new column (say column A).

Put "Carters Despatch" (or Carter's Dispatch????) in A1
then put this in A2:
=countif(b2:x2,$a$1)

This will give you a count of how many times the text in A1 appears in B2:X2
(change X to your last column--D if your data really is only 3 columns after
Data|Text to columns).

Then apply Data|filter|Autofilter to column A
Filter to show anything greater than 0.

If you need to search for a different value, just show all the data
(data|Filter|show all) and then type in the new value in A1 and filter to show
greater than 0.


Janet BN wrote:

Hi,

I have a column of data from MSProject which has comma seperated values in
one column. I have used the "Text to Columns" feature but now I need to do a
lookup of the 3 columns and select all instances of a particular value.

i.e. the original column data reads

x Builder,Carters Despatch,Carters Delivery
Carters Despatch
Jack Halsey,Hoults Doors,Carters Despatch

Once seperated I need to display all instances of "Carters Despatch"
regardless of the column they are in.

If there is a better approach whoopee, otherwise can someone please let me
know how I so the selection.

Any help appeciated - thanks.

--

Dave Peterson


--

Dave Peterson