Data filtering using formulas > Openoffice formula does not work in excel

[EvilC]

Hello all,

I have used Openoffice Calc to make some reports. Now the company gave me excel to work with which i was excited about.
The problem is that some of my OO (OpenOffice) formulas don't do the trick anymore, and i'm not that great in formula debugging so thats why i turn to your expertise

The following issue occurs:

1	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	Total contacts				0	Filtered			Total contacts				0	Filtered
3	Telephone in	4.545	12	TRUE	#N/A				Telephone in	4.545	12	TRUE	1	Telephone in	4545
4	Work Order	2.128		FALSE					Work Order	2.128		FALSE	3	Internet	1259
5	Internet	1.259		TRUE					Internet	1.259		TRUE	4	Chat	721
6	Chat	721		TRUE					Chat	721		TRUE	5	E-mail	532
7	E-mail	532		TRUE					E-mail	532		TRUE	9	Letter in	2
8	E-mail out	238		FALSE					E-mail out	238		FALSE	#N/A
9	Letter out	71		FALSE					Letter out	71		FALSE	#N/A
10	Telephone out	26		FALSE					Telephone out	26		FALSE	#N/A
11	Letter in	2		TRUE					Letter in	2		TRUE	#N/A
12	0	0		FALSE					0	0		FALSE	#N/A
13	0	0		FALSE					0	0		FALSE	#N/A
14	0	0		FALSE					0	0		FALSE	#N/A
15

<tbody>
</tbody>

I extract data with two columns, Subject (A column) and Count (B column). I filter the subject and use: =OR(A3="Telephone in"; A3="Chat"; A3="Internet"; A3="E-Mail"; A3="Letter in") in D3. This gives met the TRUE or FALSE values. In E2 i used: =E2+MATCH(1;OFFSET($D$3;E2;0;$C$3-E2;1);0)where F2 is zero value. This returned the position of each TRUE value in the D column. After this i just need to copy the INDEX from the corresponding TRUE values into G column using: =IF(ISERROR(E3);"";INDEX(A$3:A$14;E3)).

Sothe problem here is the =E2+MATCH(1;OFFSET($E$3;E2;0;$D$3-E2;1);0) formula. Does anyone know what goes wrong here??

The I to O columns is what shows when working.

Hope someone can help me out here.

Regards,
Evil

 

[EvilC]

Really, country depending?? Im in Europe, Portugal

 

[Aladin Akyurek]

@ Aladin: Data extraction is column A:Source and column B:count. Now column a could look different depending on the extraction. Thats why i need to filter the source first. In above example column A and B are raw data. Column N adn O is what i need to see.

@ Andrew, can you tell me if there is another option to get all TRUE statements from column D in a ascending order?


Thanks for your help guys.

Could Letter in occur more than once in the data area? If it does, shoult that be listed just once in the result list?

 

[Andrew Poulsom]

Yes, it depends on your Windows Regional Settings, as do date and time formats, and the thousands and decimal separators.

 

[EvilC]

Now that we are solving this i run into another issue i cant seem to solve.....
After sorting the data i detract the data to get a weekly amount available. I then need to put the values into ascending order.

In openoffice i use this formula:

=SUMPRODUCT(K3<$K$3:$K$7)+SUMPRODUCT(K3=K$3:$K3)

Where K3 to K7 has numeric values and returns me the position in numbers.

Any ideas?

Really need to pickup a excel book !!

Regards,
Artur

 

[Andrew Poulsom]

In Excel you need to coerce TRUE/FALSE into 1/0. One way is:

=SUMPRODUCT(--(K3<$K$3:$K$7))+SUMPRODUCT(--(K3=K$3:$K3))

 

[EvilC]

Okay, that i did not know,
-- does the same as adding a 0??

 

[Andrew Poulsom]

-- does the same as adding a 0??

Yes.

 

[EvilC]

Great guys. Helped me out.

Thanks Andrew

@ Aladin, thanks for you effort, but it is solved. Thanks again

 

[Aladin Akyurek]

Great guys. Helped me out.

Thanks Andrew

@ Aladin, thanks for you effort, but it is solved. Thanks again

I have no clue for I had something different in mind... Thanks for the feedback though.

 

[EvilC]

I have no clue for I had something different in mind... Thanks for the feedback though.

Well i had this worksheet already in openoffice, just some miner differences puting it into excel.

Maar bedankt in ieder geval