CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
MALKAVIAN
MALKAVIAN

Reputation: 131

Sort excel rows according to list

Hi I have a excel spreadsheet (A) that looks like below. This list goes on to thousands:

BAR CODE | SERIAL NO. | DEVICE NO.| CELL NO.| CLIENT DETAILS | STATUS
 xxxxxx  |  xxxxxxx   |  0001     |   xxxx  |   xxxxxxxxxx   | xxxxxx
 xxxxxx  |  xxxxxxx   |  0002     |   xxxx  |   xxxxxxxxxx   | xxxxxx
 xxxxxx  |  xxxxxxx   |  0003     |   xxxx  |   xxxxxxxxxx   | xxxxxx
 xxxxxx  |  xxxxxxx   |  0004     |   xxxx  |   xxxxxxxxxx   | xxxxxx
 xxxxxx  |  xxxxxxx   |  0005     |   xxxx  |   xxxxxxxxxx   | xxxxxx
 xxxxxx  |  xxxxxxx   |  0006     |   xxxx  |   xxxxxxxxxx   | xxxxxx
 xxxxxx  |  xxxxxxx   |  0007     |   xxxx  |   xxxxxxxxxx   | xxxxxx
 xxxxxx  |  xxxxxxx   |  0008     |   xxxx  |   xxxxxxxxxx   | xxxxxx

I also have a list of the device numbers (B) like below:

00001
00003
00007
00008

I want to use the list of device numbers (B) to search the spreadsheet(A) for those specific numbers and then return the whole row containing those numbers.

An example of the outcome I want would be:

BAR CODE | SERIAL NO. | DEVICE NO.| CELL NO.| CLIENT DETAILS | STATUS
 xxxxxx  |  xxxxxxx   |  0001     |   xxxx  |   xxxxxxxxxx   | xxxxxx
 xxxxxx  |  xxxxxxx   |  0003     |   xxxx  |   xxxxxxxxxx   | xxxxxx
 xxxxxx  |  xxxxxxx   |  0007     |   xxxx  |   xxxxxxxxxx   | xxxxxx
 xxxxxx  |  xxxxxxx   |  0008     |   xxxx  |   xxxxxxxxxx   | xxxxxx

I have tried using the Filter tab and sort & filter but I have not been able to find a way to achieve my desired outcome.

Any help or suggestions will make me very happy thanks.

Upvotes: 0

Views: 99

Answers (2)

EEM
EEM

Reputation: 6659

Use this formula:

=INDEX(SrcRng,MATCH(DevNo,DevNoCol,0),MATCH(Title,SrcHdr,0))

Where:

SrcRng: Is the Range holding the source data.

DevNo: Is the DEVICE NO. from the result range.

DevNoCol: Is the DEVICE NO. column range form the source range.

Title: Is the corresponding cell in the header row from the target range (1st Row).

SrcHdr: Is the header from the source range (1st Row).

So assuming the source data in worksheet A is located at 'A1:F3000' and the result table is located in worksheet B at 'A1:F4'; the formula to apply would be:

=INDEX(A!$A$1:$E$3000,MATCH($C2,A!$C$1:$C$3000,0),MATCH(A$1,A!$A$1:$E$1,0))

Copy this formula in Columns A, B, D and E in the result table.

enter image description here

Upvotes: 1

Lucas Poloni Cordeiro
Lucas Poloni Cordeiro

Reputation: 36

You can create a new Table and use VLookup to "sort" it

Original Table

BAR CODE | SERIAL NO. | DEVICE NO.| CELL NO.| CLIENT DETAILS | STATUS xxxxxx | xxxxxxx | 0001 | xxxx | xxxxxxxxxx | xxxxxx xxxxxx | xxxxxxx | 0002 | xxxx | xxxxxxxxxx | xxxxxx xxxxxx | xxxxxxx | 0003 | xxxx | xxxxxxxxxx | xxxxxx xxxxxx | xxxxxxx | 0004 | xxxx | xxxxxxxxxx | xxxxxx xxxxxx | xxxxxxx | 0005 | xxxx | xxxxxxxxxx | xxxxxx xxxxxx | xxxxxxx | 0006 | xxxx | xxxxxxxxxx | xxxxxx xxxxxx | xxxxxxx | 0007 | xxxx | xxxxxxxxxx | xxxxxx xxxxxx | xxxxxxx | 0008 | xxxx | xxxxxxxxxx | xxxxxx

This will be your new table (sorting already what you need) A | B | C | D | E | F BAR CODE | SERIAL NO. | DEVICE NO.| CELL NO.| CLIENT DETAILS | STATUS xxxxxx | xxxxxxx | 0001 | xxxx | xxxxxxxxxx | xxxxxx xxxxxx | xxxxxxx | 0003 | xxxx | xxxxxxxxxx | xxxxxx xxxxxx | xxxxxxx | 0007 | xxxx | xxxxxxxxxx | xxxxxx xxxxxx | xxxxxxx | 0008 | xxxx | xxxxxxxxxx | xxxxxx

So you will do use =VLOOKUP(DEVICE NO., Entire Table selection, Column you want to match, 0):

So for column:

A: VLOOKUP(C2, A$1:F$#, 1, 0)

B: VLOOKUP(C2, A$1:F$#, 2, 0)

D: VLOOKUP(C2, A$1:F$#, 4, 0)

E: VLOOKUP(C2, A$1:F$#, 5, 0)

("#" is the number of rows your original table have, which I have no clue)

Then you just expand the formulas for the amount of devices you want to sort. This should definitely solve your problem. Although, if there is any question, please let me know.

Upvotes: 0

Related Questions