Sorting to match master column

  • Thread starter oldguywithbadeyes
  • Start date
O

oldguywithbadeyes

Looks like this;

A B C D E F g H
50000 valve 50120 ring 50118 nut 50113 washer
50001 bolt
50002 leg
50110 spacer
50113 washer
50116 oin
50118 nut
50120 ring
Needs to look like this;
A | B | C | D E F G
H
50000 | valve | |
50002 | leg | |
50110 | spacer | |
50113 | washer | 50113 | washer
50116 | oin | 50118 | nut
50118 | nut | 50120 | ring
50120 | ring | |

I have 440 rows and 96 colums to sort . HHHHEEELLLLPPPP!
Thanks
meagain
 
M

Miguel Zapico

I may suggest one approach, that I don't know if it will fit you. The
premise is that all the numbers and words related as always the same (50120
is always a ring, not anything else).
With that, I would copy the columns A:B to a new sheet, and on column C on
that new sheet I would do a formula like:
=COUNTIF(original!$C$1:$DD$440,A1)
You may need to change the "original" range to fit your exact case. With
that you will have for each code the number of times that it is repeated on
the matrix. Now for creating the columns you can craft simple "IF"
statements in the new sheet, for example in columns D, E, F, G:
=IF($C1=2,A1,"")
=IF($C1=2,A2,"")
=IF($C1=3,A2,"")
=IF($C1=3,A2,"")
Repeating the formula as needed.

Hope this helps,
Miguel.
 
M

Miguel Zapico

Well, forgeting about the formulas used, the idea is count the number of
times that each number appears on the area that you have to sort, and based
on that and the master column, generate the information instead of moving it.
The operations that I choose were:
1. Copy the master columns (A and B) to other place
2. Use a formula to count the appearance of each code (the COUNTIF formula)
3. Use some formulas to recreate the data (the IF formulas)

The formulas support this approach, but it may not be the unique one. I
cannot think on other simpler, sorry.

Miguel.
 
Top