Excel sort

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

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

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

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

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)

I meant "Rephrasing"...

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:

   =IF(OR(B2="",ISNA(MATCH(C2,B$2:B$1000,FALSE))),"",HYPERLINK(IF(CELL("filename",$A$1)="","",MID(CELL("filename",$A$1),SEARCH("[",CELL("filename",$A$1)),SEARCH("]",CELL("filename",$A$1))-SEARCH("[",CELL("filename",$A$1))+1))&"'"&MID(CELL("filename",$A$1),SEARCH("]",CELL("filename"))+1,31)&"'!"&ADDRESS(MATCH(C2,B$2:B$1000,FALSE)+1,3),INDEX(C$2:C$1000,MATCH(C2,B$2:B$1000,FALSE))))

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

Kevin

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

please ignore the above

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....    

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

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

Good-morning MAds and everyone else,

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

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.