• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 320
  • Last Modified:

Excel sort

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
MrMay
Asked:
MrMay
  • 5
  • 5
  • 3
  • +2
1 Solution
 
jo_mCommented:


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
 
MAdSCommented:
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
 
MrMayAuthor Commented:
Hey MAds...
I don't understand your step 4 and 5.. can u please explain....

0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
Patrick MatthewsCommented:
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
 
MAdSCommented:
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
 
MAdSCommented:
I meant "Rephrasing"...
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
0
 
MrMayAuthor Commented:
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
 
MrMayAuthor Commented:
please ignore the above
0
 
MrMayAuthor Commented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
MAdSCommented:
That's what vlookup does. Have you tried? How it didn' work?
0
 
MrMayAuthor Commented:
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
0
 
zorvek (Kevin Jones)ConsultantCommented:
Using MATCH and INDEX.

Kevin
vlookupsample.xlsx
0
 
MAdSCommented:
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

Featured Post

Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

  • 5
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now