Microsoft Excel Transpose Based on Date and Cell Value

Question

I've got raw data like this:

01-01-17    Cash Sales  1500
01-01-17    POS 1       50
01-01-17    POS 2       30
02-01-17    Cash Sales  6000
02-01-17    POS 1       150
02-01-17    POS 2       95
03-01-17    Cash Sales  6000
03-01-17    POS 1       150
04-01-17    Cash Sales  2700
04-01-17    POS 2       600
05-01-17    Cash Sales  1000
06-01-17    Cash Sales  7000
06-01-17    POS 1       1000

I'm trying to deduct the POS 1 and POS 2 sales of each day from the Cash Sales for that same day.

How can I achieve this in Excel?

I've looked up the Transpose function, but seems like that won't do for this.

The end results I'm trying to achieve are like below. Can you help?

                                    Cash Sales  POS 1   POS 2
01-01-17    Cash Sales  1500        1500        50      30
01-01-17    POS 1       50              
01-01-17    POS 2       30              
02-01-17    Cash Sales  6000        6000        150     95
02-01-17    POS 1       150             
02-01-17    POS 2       95              
03-01-17    Cash Sales  6000        6000        150 
03-01-17    POS 1       150             
04-01-17    Cash Sales  2700        2700                600
04-01-17    POS 2       600             
05-01-17    Cash Sales  1000        1000        
06-01-17    Cash Sales  7000        7000        1000    
06-01-17    POS 1       1000

Do you want the POS1 and POS2 also below cash sales? or just the cash sales for each day would be fine? (talking about second column) — Gowtham Shiva
– Gowtham Shiva, Commented May 13, 2017 at 7:26
The POS 1 and POS 2 are already in the RAW data. I'm trying to get them as seen in the second code block. — Norman
– Norman, Commented May 13, 2017 at 7:28
The output should be just to the right of the input as show, or want it in a different sheet? — Gowtham Shiva
– Gowtham Shiva, Commented May 13, 2017 at 7:36
It's in three columns as in the first code block (raw data). The second code block is the desired results. — Norman
– Norman, Commented May 13, 2017 at 7:46

Gowtham Shiva · Accepted Answer · 2017-05-13 07:50:17Z

2

Can you try this and let me know if it works,

Formula starting from D2, drag to right and drag down to the complete range,

=IFERROR(IF(AND($B2=$D$1,INDEX($A2:$C$999,MATCH(D$1,$B2:$B$999,0),1)=$A2),INDEX($B2:$C$999,MATCH(D$1,$B2:$B$999,0),2),""),"")

answered May 13, 2017 at 7:50

Gowtham Shiva

3,8852 gold badges13 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Norman Over a year ago

Thanks, This did the job :-)

Collectives™ on Stack Overflow

Microsoft Excel Transpose Based on Date and Cell Value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related