organizing data from one sheet to others

Hi everyone,

I have a set of data mixed up. I want to filter that data, from the main sheet to new sheets. The criteria is to filter by car brands (RENAULT, VW, MERCEDES...) so there will be a sheet for each brand. At the same time, as each file has a date, I have to copy the file from the main sheet, in the month related.

I understand that this explanation is not totally clear, but I attached a excel document where you can see what I want to do. I did the sheet called 'RENAULT' as an example by hand.

Could you help me with this problem?


Thank you very much in advance.

Your example has only one car per make per month but reality is there will be more than 1. (?)

Yes, it could be... I try to do it as a first step one car per month or even no car in some months. But the next step could be that there are two cars in the same month. In this case, I should sum up the numbers and calculate the average.

Tow points:

1. It would be sensible to have headings on your tables.

2. What data is be averaged?

Re-post you file with an example of 2 or more cars in a month showing any calculations (and results of) required.

1. I put the headins as you suggested. I am sorry

2. The data that should be avaraged are those with numbers, e.i., colums 'cars' and 'liters'. In the case of two cars in the same month, has it happens with RENAULT in July, the colums 'Color', 'Shift' and 'Date' should be empty or with the question mark in the celd. The other two colums 'Cars' and 'Liters' are the average of the two data from July.

the document

Just to be clear:

If there is more than one car in month, there is only on entry per month in the Make tab (irrespective of colour etc) with an average figure for "Cars" and "Litres"

exactly, in the 'main sheet of data' could be more than a entry in the same month per car. But once you go to the 'RENAULT' sheet (for instance), then you just have an entry per month. The data to be averaged are 'Cars' and 'Liters'. The other fields are empty or with the 'question mark' because in this case, I don't know what I will have to put at the end.

See attached:

In the Main Sheet I moved the brand to column A. I suggest you don't need brand in the "Makes" sheets.

Look at the "Renault (2)" sheet:

In Column A I set the dates to date format (01/01/2015) and formatted as "mmmm". I have assumed data is always for ONE year (2015) in this case.

in B2

=IF(COUNTIFS(TODOS!$D$2:$D$28,">="&$A2,TODOS!$D$2:$D$28,"<="&EOMONTH($A2,0),TODOS!$A$2:$A$28,"RENAULT")=1,INDEX(TODOS!$B$2:$B$28,MATCH(1,(TODOS!$A$2:$A$28="RENAULT")*(MONTH(TODOS!$D$2:$D$28)=MONTH($A2)),0))," ")

in C2

=IF(COUNTIFS(TODOS!$D$2:$D$28,">="&$A2,TODOS!$D$2:$D$28,"<="&EOMONTH($A2,0),TODOS!$A$2:$A$28,"RENAULT")=1,INDEX(TODOS!$C$2:$C$28,MATCH(1,(TODOS!$A$2:$A$28="RENAULT")*(MONTH(TODOS!$D$2:$D$28)=MONTH($A2)),0))," ")

in D2

=IF(COUNTIFS(TODOS!$D$2:$D$28,">="&$A2,TODOS!$D$2:$D$28,"<="&EOMONTH($A2,0),TODOS!$A$2:$A$28,"RENAULT")=1,INDEX(TODOS!$D$2:$D$28,MATCH(1,(TODOS!$A$2:$A$28="RENAULT")*(MONTH(TODOS!$D$2:$D$28)=MONTH($A2)),0))," ")

Enter all of the above with Ctrl+Shift+Enter then copy down

in F2

=IFERROR(AVERAGEIFS(TODOS!E$2:E$28,TODOS!$D$2:$D$28,">="&$A2,TODOS!$D$2:$D$28,"<="&EOMONTH($A2,0),TODOS!$A$2:$A$28,"RENAULT"),"")

in G2

=IFERROR(AVERAGEIFS(TODOS!F$2:F$28,TODOS!$D$2:$D$28,">="&$A5,TODOS!$D$2:$D$28,"<="&EOMONTH($A5,0),TODOS!$A$2:$A$28,"RENAULT"),"")

You can copy sheet and change "RENAULT" to "VW" etc