# Need Help Extracting Data from One Table to Another and Sorting the Reults; No VBA

##### New Member
Hello,

I am trying to extract some data and place it in another part of the workbook, and sort it. Example:

Column A has a title of "Cost" (A1), with the values being (A2:A6): \$0, \$0, \$50.50, \$0, \$46

Column B has a title of "Unique Number" (B1), with values of (B2:B6): 1, 2, 3, 4, 5

Column C has a title of "Date" (C1), with the following values (C2:C6): 4/21, 5/11, 5/27, 6/4, 7/22

I want to extract data from all three cells into a new table, but only the cells where the cost is greater than zero. And I want to the new table to be sorted from lowest cost to greatest cost. The final table should look like:

Column L: \$46, \$50.50
Column M: 5, 3
Column N: 7/22, 5/27

The key is I don't want to use VBA. Can anyone help? Thanks in advance!

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.
Hi and welcome to MrExcel.

I'm sorry, but I cannot give you a Sorted solution, but will this help for now?...

Excel Workbook
ABCDEFGHIJKLMNO
1CostUnique NumberDate*******CriteriaCostUnique NumberDate*
20121/04/2013*******050.5327/05/2013*
30211/05/2013********46522/07/2013*
450.5327/05/2013************
50404/06/2013************
646522/07/2013************
7***************
Sheet2

The formula in L2 needs to be entered with ctrl shift enter NOT just enter, it can then be copied across and down.
You will obviously need to change the cell references to suit your layout.
I'm sure there is a Sorted solution available, but I cannot think how to go about constructing it, either with Frequency or the aid of a "Helper" column, I guess. Or, you could just sort the data in your table by Cost, smallest to largest!!

There may be some help here....

excelisfun -- Excel How To Videos - YouTube

Sort Values | Get Digital Help

I'm out of time now, so good luck and I hope someone jumps in with the solution you require.

Ak

Last edited:
Hi Akashwani,

Thank you for your post! That will definitely get me started and closer to my goal, but I will eventually need to have the cost sorted. Doing it manually is not an option since I will be using a solver function later.

Do you know of a way to take the output you provided and sort the values into another set of columns (P, Q, & R)? There's plenty of room.

Hi,

Perhaps like this....

Excel Workbook
ABCDEFGHIJKLMNOPQRS
1CostUnique NumberDateCriteriaCostUnique NumberDateCostUnique NumberDate
250.5327/05/2013050.5327/05/201346522/07/2013
346522/07/201346522/07/201350.5327/05/2013
40121/04/2013
50211/05/2013
60404/06/2013
7
Sheet2

The formula in L2 needs to be entered with ctrl shift enter NOT just enter, it can then be copied across and down.
The formula in P2 needs to be copied down.
The formula in Q2 needs to be coped across and down.
You will obviously need to change the cell references to suit your layout.

**I don't know how to provide a solution IF you have more than one number the same in column L**

I hope that helps.

Ak

Hello,

I am trying to extract some data and place it in another part of the workbook, and sort it. Example:

Column A has a title of "Cost" (A1), with the values being (A2:A6): \$0, \$0, \$50.50, \$0, \$46

Column B has a title of "Unique Number" (B1), with values of (B2:B6): 1, 2, 3, 4, 5

Column C has a title of "Date" (C1), with the following values (C2:C6): 4/21, 5/11, 5/27, 6/4, 7/22

I want to extract data from all three cells into a new table, but only the cells where the cost is greater than zero. And I want to the new table to be sorted from lowest cost to greatest cost. The final table should look like:

Column L: \$46, \$50.50
Column M: 5, 3
Column N: 7/22, 5/27

The key is I don't want to use VBA. Can anyone help? Thanks in advance!

A:C houses the data, L:N the processing...

 Cost Unique Number Date Cost Unique Number Date 0 1 4/21 46 5 7/22 0 2 5/21 46 7 7/23 50.5 3 5/27 50 6 7/23 0 4 6/4 50.5 3 5/27 46 5 7/22 50 6 7/23 46 7 7/23

<colgroup><col style="width: 48pt;" width="64"> <col style="width: 98pt; mso-width-source: userset; mso-width-alt: 4636;" width="130"> <col style="width: 52pt; mso-width-source: userset; mso-width-alt: 2446;" width="69"> <col style="width: 48pt;" span="2" width="64"> <col style="width: 88pt; mso-width-source: userset; mso-width-alt: 4152;" width="117"> <col style="width: 48pt;" width="64"> <tbody>
</tbody>

L2, control+shift+enter, not just enter, and copy down:
Rich (BB code):
``=IFERROR(SMALL(IF(\$A\$2:\$A\$8,\$A\$2:\$A\$8),ROWS(\$L\$2:L2)),"")``

M2, control+shift+enter and copy down:
Rich (BB code):
``````=IF(L2="","",INDEX(\$B\$2:\$B\$8,SMALL(IF(\$A\$2:\$A\$8=L2,
ROW(\$A\$2:\$A\$8)-ROW(\$A\$2)+1),COUNTIF(\$L\$2:L2,L2))))``````

N2, just enter and copy down:
Rich (BB code):
``=IF(M2="","",INDEX(\$C\$2:\$C\$8,MATCH(M2,\$B\$2:\$B\$8,0)))``

Thank you very much for dropping in and solving this.

Thanks

Ak

Big thanks, Aladin! That did the job, though I later realized I needed the Cost column to sort in descending order (changed the SMALL function to LARGE).

I have been butting my head against a similar issue with the same spreadsheet.

Here it is:
Now that I have my data sorted in columns L:N, I want to return the value from column M that meets the following criteria:
1. Lowest cost where Cost (L2:L4) is greater than or equal to H2
2. Date (N2:N4) is less than or equal to E2
3. Unique Number (M2:M4) has not been used previously. I am keeping a table of used unique numbers in AC2:AS2.

So, using the values in the spreadsheet above with the ones below:

• H2=48
• E2=8/10
• AF2=6

The result should be 3.

I'm able to get parts one and two to work, but I'm stumped on three. Here's what I have done to make one and two work:
INDEX(\$M\$2:\$M\$4,MATCH(\$H4,IF(\$N\$2:\$N\$4<=\$E4,\$L2:\$L\$4),-1))

I've tried a number of things to make part three work, but have been striking out. Any ideas? Thanks!

Sorry, it should be \$H2 in the quote above, and I'm doing ctrl+shift+enter.

INDEX(\$M\$2:\$M\$4,MATCH(\$H2,IF(\$N\$2:\$N\$4<=\$E4,\$L2:\$L\$4),-1))

*friendly bump*

Big thanks, Aladin! That did the job, though I later realized I needed the Cost column to sort in descending order (changed the SMALL function to LARGE).

You are welcome.

I have been butting my head against a similar issue with the same spreadsheet.

Here it is:
Now that I have my data sorted in columns L:N, I want to return the value from column M that meets the following criteria:
1. Lowest cost where Cost (L2:L4) is greater than or equal to H2
2. Date (N2:N4) is less than or equal to E2
3. Unique Number (M2:M4) has not been used previously. I am keeping a table of used unique numbers in AC2:AS2.

So, using the values in the spreadsheet above with the ones below:

• H2=48
• E2=8/10
• AF2=6

The result should be 3.

I'm able to get parts one and two to work, but I'm stumped on three. Here's what I have done to make one and two work:

I've tried a number of things to make part three work, but have been striking out. Any ideas? Thanks!

Control+shift+enter, not just enter:
Rich (BB code):
``````=INDEX(\$M\$2:\$M\$5,MATCH(1,IF(\$L\$2:\$L\$5>=H2,IF(\$N\$2:\$N\$5<=E2,
IF(ISNA(MATCH(\$M\$2:\$M\$5,\$AC\$2:\$AS\$2,0)),1))),0))``````

Replies
5
Views
170
Replies
2
Views
145
Replies
5
Views
114
Replies
0
Views
144
Replies
0
Views
456

1,196,057
Messages
6,013,165
Members
441,751
Latest member
336448

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back