How to combine two columns with certain conditions?

Islandic · #1 07-01-2009, 03:33 PM

Hi all,

I have a small but delicate problem, that I want to solve without using VBA. I have two pairs of data. The first pair consists of the "price" and "NR of sales" of items X and the second pair consists of the "price" and "NR in stock"of items X. The colums try to predict the number in stock and sales at at a certain market price.

Now what I want to do is to merge both pairs into one pair of data with three columns, and apply the following conditions.
-for every price there must be a "NR of sales" & "NR in stock"
-when one of the other data sets does not have a price that the other pair has, then for "NR of Sales" it needs to fill in a "NR of sales" number from pair 1 with a price higher than the price in the new merged columns. For a "NR in stock" value this should be a lower number.

The example should make this clear:

Pair 1:

Price;NR of Sales
$18.00;50000
$19.00;45000
$20.00;37000
$24.00;32000
$28.00;26000
$32.00;25000

Price;NR in Stock
$31.00;45000
$29.00;36000
$28.00;28000
$27.00;22000
$24.00;16000
$19.00;12000
$10.00;5000

Now I want to merge these two pairs, and the merged pair should look like the following:
PRICE;NR of SALES; NR in Stock
$10.00;50000;5000
$18.00;50000;5000
$19.00;45000;12000
$20.00;37000;16000
$24.00;32000;16000
$27.00;26000;22000
$28.00;26000;28000
$29.00;25000;36000
$31.00;25000;45000
$32.00;25000;45000

I also attached a spreadsheet for your convenience,

Thanks!

ManyQuestions · #2 07-01-2009, 10:42 PM

Takes a while to type but is Very fast and simple if you read my way of compairing. You can easily do this in many columns if I follow your questions correctly.

First click a blank cell in a blank column, now hit the field plus on the 10 key, now click one of the cells with the information in it that you need such as the price, now hit minus on the 10 key or asteric or / for multiply or devide or plus key located next to the backspace key to add, next click the cell you want to compair price with and hit enter. once done you should have the difference in price minus price or price devided by price in the first blank cell you clicked in. Now click on that cell and copy, then black out the whole column there and click paste, excel compairs them all for you at once.

JBeaucaire · #3 07-02-2009, 12:02 AM

Ok, your tables needed the missing ZERO values, and they needed to both be in ascending order, and with that, simple worksheet functions do the rest. Index/Match...

Have a look.

Islandic · #4 07-02-2009, 04:09 AM

Excellent that seems to work. Now I also need the price column in the Pairs
Merged automated, grabbing its data from the price range in PAIR 1 and Pair 2, any idea how to do this.

I tried to copy and sorted the data ascending, but I get double prices (e.g. $19.00) that do not belong there, and only need to be single.

Thanks

JBeaucaire · #5 07-02-2009, 09:05 AM

Please post your new needs / issues in a viewable document so my weak old brain can fathom it more clearly. Thanks.

Welcome to the Excel Forum

Forum Statistics