combining columns

  • Thread starter Thread starter Scot B
  • Start date Start date
S

Scot B

Greetings,

Struggling to figure something out. Thank you for your help...

I have a column of 1200 fields. examples are "item 1", "item 2"... the big
goal is to get one list that says "order item 1", "receive item 1", "install
item 1"...

So, I can use the concatenate function to generate columns of "order item
1", "order item 2" or "receive item 1", "receive item 2".

Now trying to combine these lists so i get one long list where all of the
actions for item 1 are listed first, then all actions for item 2 are
listed....

Is there a function that accomplishes this? Or, am I thinking about this
wrong?

Thanks for your advice!

Cheers,

Scot B
 
Hi!

Here's an idea:

How many different conditions are there
like "order", "install" and "receive" ?

If there aren't more than 8 ...

Copy>Paste Special>Values on your concatenated columns and
make them constants.

Then Cut>Paste those column values to a single column so
that you have one column that contains all of the
conditions.

Assume that location is column A, starting in A1.

In some location list all your conditions. I'll use J1:J3:

J1 = order item
J2 = receive item
J3 = install item

Now, in B1 enter this formula and copy down as needed:

=--SUBSTITUTE(SUBSTITUTE(SUBSTITUTE
(A1,J$1,""),J$2,""),J$3,"")

This will return values like:

1
3
2
2
3
2
1

Now, select both columns A and B and then sort both on
column B ascending.

That will put all items in their proper item number order.
Not sure how you'd get them in order like this though:

install
order
receive
install
order
receive

Substitute is case sensitive so make sure all your values
are in the same case.

Biff
 
Back
Top