4 columns, two are dates and two are values. Can i combine these 4 into 2?

[jumbles]

so on one sheet i have dates and profit for that day

then another sheet i have the same thing

I want to combine them so i can graph them in one graph.

A B C D
5/2/13 5
5/1/13 14 5/3/13 1
5/3/13 2 5/4/13 3

To:

A

5/1/13 14
5/2/13 5
5/3/13 2
5/3/13 1
5/4/13 3

Doable?

[AZ-XL]

easiest way is, copy another sheet data and paste it into same sheet and then sort dates from smallest to largest

[Jarko28]

post a sample or explain how the data is arranged in your source sheets...what's in columns A, B, C, and D???

[jumbles]

jarko, thanks for your attention.

say we're selling hamburgers and hotdogs

column a is the date of hotdog sales
column b is the profit off of the hotdog sale

column c is the date of hamburger sales
column d is the profit made off of the hamburger sale


What I'd like to do is put data from both groups into two new colums

Column e: date of hamburger or hotdog sale
Column f: profit of hamburger or hotdog sale

AZ: I would like to do this without having to manually copy and sort data.

[Fotis1991]

Questions:

A1=01/05/2013 B1=10 C1=01/05/2013 D1=12

A2=02/05/2013 B2=20 C1=03/05/2013 D2=15

Which are the expected results in E1,F1, E2,F2 ???

[jumbles]

Folts,

E1, E2, E3, E4 would then be

01/05/2013
01/05/2013
02/05/2013
03/05/2013

F1, F2, F3, F4 would be

10
12
20
15


I'm just trying to sort two date columns into one column, and have their assigned profit values next to them

[oeldere]

Post an excel example, without confidentional information.

Also add the desired (expected) result.

If you gonna change the format of your data,
you don't need the information of hotdogs or hamburger in a column next to the date and value?

What you gonna do, with the new data?

[jumbles]

Attached is an example. Thanks for the help.

[Fotis1991]

=IFERROR(INDEX($A$2:$A$4;ROWS(I$1:$I1)); IFERROR(INDEX($C$2:$C$4; ROWS(I$1:$I1)-ROWS($A$2:$A$4)); ""))

This formula joins the 2 date lists to 1. Unfortunnately does not sort these..

If it's OK, then use this formula to get the profits

=IF(I2="";"";IFERROR(IFERROR(IF(COUNTIF(I$2:$I2;I2)=1;INDEX($B$2:$B$10;MATCH(I2;$A$2:$A$10;0));IF(COUNTIF(I$2:$I2;I2)>1;INDEX($D$2:$D$10;MATCH(I2;$C$2:$C$10;0))));INDEX($D$2:$D$10;MATCH(I2;$C$2:$C$10;0)));""))

If you need sorted by date then you have 2 options.

1) Copy>>Special Paste>Only values and sort the list..OR

2)Hide the first unsorted list and use this ARRAY formula to get a new sorted list.

=IFERROR(INDEX(List;MATCH(SMALL(COUNTIF(List;"<"&List);ROW(1:1));COUNTIF(List;"<"&List);0));"")

Then use the second formula to get your values for eatch date.

[jumbles]

thanks, fotis!

It works, but I can't figure out how to add more cells into the list for the autosorting.

[Fotis1991]

So in this case we have to use Dynamic Named Ranges. I understand that perhaps you don't know how to use these in your real workbook, so pls see this excellent article about it.

http://www.ozgrid.com/Excel/DynamicRanges.htm

See in my example how to use it. Add as many dates as you want in column A & C and you'll get your results without to do anything else.

=IFERROR(INDEX(HamList;ROWS(I$1:$I1)); IFERROR(INDEX(HotList; ROWS(I$1:$I1)-ROWS(HamList)); ""))

=OFFSET(Sheet1!$I$2;0;0;COUNT(Sheet1!$I:$I);1)>>For Dynamic Named Range

Let me know if you did it..

[jumbles]

Thanks!

How can we handle duplicate dates with different values?

I've attached the example