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

FullMoonMadness

New Member
Joined
Apr 18, 2013
Messages
5
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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
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:
Upvote 0
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. :)
 
Upvote 0
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
 
Upvote 0
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...

CostUnique NumberDate CostUnique NumberDate
014/21 4657/22
025/21 4677/23
50.535/27 5067/23
046/4 50.535/27
4657/22
5067/23
4677/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)))
 
Upvote 0
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!
 
Upvote 0
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))
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top