Solved

# Excel sort

Posted on 2011-10-07
295 Views
Hello,

I'm stuck on an excel problem... I know what to do I just don't know how to do it.

so here is my problem.
I have a table of about 800 rows.
something like this
PartNumber              Location              NewLocation
PartA                               L09                          R9
PartB                               L08                          Q3
PartC                               K1                            R38

I need to sort this spreadshet in such a way that if the first row has new location as R9 then the next row will have Location as R9.... and whatever the newLocation for that part is, it will be the Location for the following row (part)
so that my table will be sorted such as this

PartNumber             Location               NewLocation
PartA                               L09                          R9
PartB                               R9                           P3
PartC                               P3                           G3

We're moving a few parts around in our department, so if I pull out a part out of lets say location 09 and the new location is R9, i now need to know where the new location for part R9 would be......      Is there a query/macro that I can run.. can someone help me out.. I have no idea how to accomplish this...
thank you
PS.... I'm using excel2007
0
Question by:MrMay

LVL 2

Expert Comment

hello there

If I have understood you correctly  you want the location to show the value of the new locationf or the value above  so if your  second location cell was  at B3 and you placed the  formula below in B3 then what ever the value in C2 would be in the location  column below

=OFFSET(B3,-1,1,1,1)

I hope I understood you

j
0

LVL 5

Expert Comment

This is not resolved by sorting - what you are doing is more like indexing. Process below requires that you data are consistent, otherwise it will break.

1. Have columns rearranged as below:
A: NewLocation
B: Location
C: PartNumber
2. Indexed columns will in columns F, G, H:
F: PartNumber
G: Location
H: NewLocation
3. F1: =C1, G1: =B1, H1: A1
4. Copy down F1..H1 to F2..H2
5. F3: =VLOOKUP(F2; A..C; 3; FALSE)
G3: =VLOOKUP(F2; A..C; 2; FALSE)
H3: =VLOOKUP(F2; A..C; 1; FALSE)
6. Copy down F3..H3 to the end of your data
0

Author Comment

I don't understand your step 4 and 5.. can u please explain....

0

LVL 92

Expert Comment

MrMay,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files to your question.

The sample file should clearly illustrate both the input and the expected output given that input.  The sample file need not be very large, but it should have enough examples to cover the expected range of values/scenarios.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible to fully and permanently delete it.  The file may also be indexed by the major search engines.

Therefore, be very careful about posting proprietary, confidential, or other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match those in the list, you can use http://www.ee-stuff.com instead, which is not officially an EE site, but is run by people connected to EE.

Patrick
0

LVL 5

Expert Comment

Rephasing
4. Copy formulas in cells F1, G1, H1 into F2, G2, H2
5. Type each command in the specified cell. For instance --
F3: =VLOOKUP(F2; A..C; 3; FALSE) => Select cell F3 and type "=VLOOKUP(F2; A..C; 3; FALSE)" (without the quotes)
0

LVL 5

Expert Comment

I meant "Rephrasing"...
0

LVL 81

Expert Comment

I agree that sorting is not the a viable solution. Here is a formula that will provide the next location for each new location value on any row. The value is displayed as a hyperlink so you can click and instantly go to that next location. To implement, enter "NextLocation" in cell D1 and then enter this formula in cell D2 and copy down to the end of the table:

This solution assumes that the part number, location, and new location are in columns A, B, and C respectively.

Kevin
0

Author Comment

I don't think vlookup will work in this situation.

Let me try to explain this. I don't think I was clear enough.

Lets say this is my table.

PartNumber    PartDescription   Location   NewLocation   Manufacturer
FK-M5            FLEX COUPLER         P4                 P1                       onic
2199                         Ring                               P3                                 P2                   onic
705.3820       "Seal Ring - 1/8
"      P5      "P3
"      bystronic
441,243       "Clamp, MOUNTINGBRACKET
"      P1      "P4
"      bystronic
310,997       "GUIDE, Spring Post - Leaf Spring Hol
"      P2      "P5
"      bystronic
0

Author Comment

0

Author Comment

MrMay:
I don't think vlookup will work in this situation.

Let me try to explain this. I don't think I was clear enough.

Lets say this is my table.

PartNumber    PartDescription  Location   NewLocation   Manufacturer
FK-M5            FLEX COUPLER         P4                 P1                  onic
2199               Ring                          P3                 P4                   onic
3820             l Ring - 1/8                  P5                 P3                   onic
243                 RACKET                   P1                 P4                   onic
997                Spring Post               P2                 P2                   onic

as you can see from the first row the NewLocation is P1. Now I would like to second row to display the row of information that has the Location as P1... and so on...   this way there is a flow.

so in the end it all looks like this....

PartNumber    PartDescription  Location   NewLocation   Manufacturer
FK-M5            FLEX COUPLER         P4                 P1                  onic
243                 RACKET                   P1                 P2                   onic
997                Spring Post               P2                 P5                   onic
3820             l Ring - 1/8                  P5                 P3                   onic
2199               Ring                          P3                 P4                   onic

Do u see how everything flows......    I need some sort of function that will take a look at the first row, grab the NewLocation and return the following row where the Newlocation(from the previous row) is the Location of the current row.  One way of arranging this is to go through my spreadsheet and copy and paste... but I don't really want to do this... there is 800 rows.

any suggestions/help would be greatly appreciated....

0

LVL 81

Expert Comment

MrMay,

In the example you gave the last row has a new location of "P4" which implies you want the first row is not in the correct location as it should be, by your rules, after row five. But then the subsequent rows also are incorrectly sorted and so on. Can you begin to see why this isn't possible?

Kevin
0

LVL 5

Expert Comment

That's what vlookup does. Have you tried? How it didn' work?
0

Author Comment

I'm still having trouble with this vlookup.
Personally I've never used vlookup. I've been doing some online learning on the function. In your code sample above you are saying that it should look like this .... =vlookup(F2:A..C: 3: False)
that syntax is completely wrong according to my online sources.... it should be =vlookup(F2,A1:C6,3,False)
Also, your 3rd step.... F1:=C1, G1:=B1....  what does that mean ?
I'm using excel 2007

please see attached for my excel .

vlookupsample.xlsx
0

LVL 81

Accepted Solution

Using MATCH and INDEX.

Kevin
vlookupsample.xlsx
0

LVL 5

Expert Comment

What a wrote was not "=vlookup(F2:A..C: 3: False)", but "=VLOOKUP(F2; A..C; 3; FALSE)". Arguments separator are language specific and in mine is ";". Yours should be ",".

Anyway I lost interest in persuing an answer to that question. Please follow-up with another expert.
Good luck.
0