Excel formula suitable for this project

B

badang_laut

New Member
Joined
Apr 19, 2018
Messages
10
NAMESIDDAYSTYPEREPORT 1REPORT 2
Abu0852922Cuti Sakit PanelABU (PANEL-2, SWASTA-4, TOTAL-5)6
Abu0852921Cuti Sakit Swasta
Abu0852921Cuti Sakit Swasta
Abu0852921Cuti Sakit Swasta
Abu0852921Cuti Sakit Swasta
Ali1250271Cuti Sakit PanelALI (PANEL-1)1
Sulaiman0857431Cuti Sakit Panel
Sulaiman0857431Cuti Sakit Panel
Sulaiman0857431Cuti Sakit Panel
Sulaiman0857431Cuti Sakit Panel
Sulaiman0857431Cuti Sakit Panel
Mamat0355791Cuti Sakit Panel
Mamat0355791Cuti Sakit Panel
Mamat0355791Cuti Sakit Panel
Tariq0863751Cuti Sakit Panel
Devi0754042Cuti Sakit Panel
Jaafar0162861Cuti Sakit Kerajaan
Wahid0157431Cuti Sakit Panel
Hamid0350951Cuti Sakit Panel
Hamid0350951Cuti Sakit Panel
Hamid0350951Cuti Sakit Panel
Rahim0351192Cuti Sakit Kerajaan
Kassim0255421Cuti Sakit Panel
Eric0752861Cuti Sakit Swasta
Eric0752861Cuti Sakit Insurans
Peter1151811Cuti Sakit Panel
Arumalai0851922Cuti Sakit Kerajaan
Arumalai0851923Cuti Sakit Kerajaan
Arumalai0851921Cuti Sakit Kerajaan
Arumalai0851922Cuti Sakit Kerajaan
Arumalai0851921Cuti Sakit Kerajaan
Arumalai0851921Cuti Sakit Panel
Arumalai0851922Cuti Sakit Panel
Arumalai0851921Cuti Sakit Panel
Arumalai0851921Cuti Sakit Panel
Arumalai0851921Cuti Sakit Panel
Arumalai0851922Cuti Sakit Panel
Arumalai0851921Cuti Sakit Panel
Arumalai0851921Cuti Sakit Panel
Arumalai0851921Cuti Sakit Panel
Arumalai0851921Cuti Sakit Panel
Arumalai0851921Cuti Sakit Panel
Arumalai0851921Cuti Sakit Panel
Arumalai0851921Cuti Sakit Panel

<tbody>
</tbody>

Hello,

I am asking for help on this project.

Need to do report 1 and 2.

eg. Abu, ID 085292

RESULT 1 = ABU (PANEL-2, SWASTA-4, TOTAL-5)

RESULT 2 = 6

What is the best formula for me to get result as above?

Thanks in advance for the help.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
1. How do you get ABU PANEL -2 ?
And how do you get a TOTAL of 5 from PANEL-2 and SWASTA-4 ?

ABU PANEL is only 1, in whcih case the TOTAL is correct.

Have you read the figures wrong and included Ali's entry ?


2. Are you only searching for the last word in the 4th column?
Are you only searching for PANEL and SWASTA and KERAJAAN and INSURAS
Or are there other words to search for that are not listed here?

3. Does the report have to be in that format or can it be pretty much any format as long as the figures are correct?
 
Upvote 0
Special-K99 said:
1. How do you get ABU PANEL -2 ?
And how do you get a TOTAL of 5 from PANEL-2 and SWASTA-4 ?

ABU PANEL is only 1, in whcih case the TOTAL is correct.

Have you read the figures wrong and included Ali's entry ?


2. Are you only searching for the last word in the 4th column?
Are you only searching for PANEL and SWASTA and KERAJAAN and INSURAS
Or are there other words to search for that are not listed here?

3. Does the report have to be in that format or can it be pretty much any format as long as the figures are correct?
Click to expand...

1. Sorry, the TOTAL is 6 not 5 (PANEL : 2 DAYS + SWASTA : 4 DAYS) for NAMES : ABU)

For NAMES : ALI only 1 names and only got PANEL : 1 DAYS)


2. First i want searching for NAMES (1st column) after thats TYPE (4th column)

3. Report as long i get how much TYPE and TOTAL for each NAMES.

Thanks in advance
 
Upvote 0
We'll try this one again

2. Are you only searching for PANEL and SWASTA and KERAJAAN and INSURAS
Or are there other words to search for that are not listed here?

If you have a set list of words to search for Pivot Table would be the way to go
 
Upvote 0
Special-K99 said:
We'll try this one again

2. Are you only searching for PANEL and SWASTA and KERAJAAN and INSURAS
Or are there other words to search for that are not listed here?

If you have a set list of words to search for Pivot Table would be the way to go
Click to expand...

Yes only searching for PANEL, SWASTA, KERAJAAN and INSURANS...

So what i do is using Pivot Table only rite?
 
Upvote 0
Pivot Table is one option.
I'm not an expert on Pivot Table

You should be able to count the number of TYPES by NAME easy.
You might be able to create a new calculated field to search the TYPE for each word PANEL SWASTA KERAJAAN INSURANS and split the report further by sorting on that field (am not sure this is possible you'll have to play around with Pivot Table to see what you can produce).

Or create a Helper column

in E2
=IF(ISNUMBER(SEARCH("PANEL",D2)),"PANEL",IF(ISNUMBER(SEARCH("SWASTA",D2)),"SWASTA",IF(ISNUMBER(SEARCH("KERAJAAN",D2)),"KERAJAAN",IF(ISNUMBER(SEARCH("INSURANS",D2)),"INSURANS","NONE FOUND"))))
and copy down

Then do a Pivot Table counting by NAME by TYPE by Column E
 
Last edited:
Upvote 0
xI7RW9.png
Special-K99 said:
Pivot Table is one option.
I'm not an expert on Pivot Table

You should be able to count the number of TYPES by NAME easy.
You might be able to create a new calculated field to search the TYPE for each word PANEL SWASTA KERAJAAN INSURANS and split the report further by sorting on that field (am not sure this is possible you'll have to play around with Pivot Table to see what you can produce).

Or create a Helper column

in E2
=IF(ISNUMBER(SEARCH("PANEL",D2)),"PANEL",IF(ISNUMBER(SEARCH("SWASTA",D2)),"SWASTA",IF(ISNUMBER(SEARCH("KERAJAAN",D2)),"KERAJAAN",IF(ISNUMBER(SEARCH("INSURANS",D2)),"INSURANS","NONE FOUND"))))
and copy down

Then do a Pivot Table counting by NAME by TYPE by Column E
Click to expand...

I have try your formula but did not get the RESULT i want.

xI7RW9.png


xI7RW9.png


xI7RW9.png
 
Upvote 0
NAMESIDDAYSTYPE 1TYPE 2REPORT 1REPORT 2
Abu0852922Cuti Sakit PanelPANELABU (PANEL-2, SWASTA-4, TOTAL-6)6
Abu0852921Cuti Sakit SwastaSWASTA
Abu0852921Cuti Sakit SwastaSWASTA
Abu0852921Cuti Sakit SwastaSWASTA
Abu0852921Cuti Sakit SwastaSWASTA
Ali1250271Cuti Sakit PanelPANELALI (PANEL-1)1
Sulaiman0857431Cuti Sakit PanelPANELSULAIMAN (PANEL-5, TOTAL-5)5
Sulaiman0857431Cuti Sakit PanelPANEL
Sulaiman0857431Cuti Sakit PanelPANEL
Sulaiman0857431Cuti Sakit PanelPANEL
Sulaiman0857431Cuti Sakit PanelPANEL
Mamat0355791Cuti Sakit PanelPANELMAMAT (PANEL-3, TOTAL-3)3
Mamat0355791Cuti Sakit PanelPANEL
Mamat0355791Cuti Sakit PanelPANEL
Tariq0863751Cuti Sakit PanelPANEL
Devi0754042Cuti Sakit PanelPANEL
Jaafar0162861Cuti Sakit KerajaanKERAJAAN
Wahid0157431Cuti Sakit PanelPANEL
Hamid0350951Cuti Sakit PanelPANEL
Hamid0350951Cuti Sakit PanelPANEL
Hamid0350951Cuti Sakit PanelPANEL
Rahim0351192Cuti Sakit KerajaanKERAJAAN
Kassim0255421Cuti Sakit PanelPANEL
Eric0752861Cuti Sakit SwastaSWASTA
Eric0752861Cuti Sakit InsuransINSURANS
Peter1151811Cuti Sakit PanelPANEL
Arumalai0851922Cuti Sakit KerajaanKERAJAAN
Arumalai0851923Cuti Sakit KerajaanKERAJAAN
Arumalai0851921Cuti Sakit KerajaanKERAJAAN
Arumalai0851922Cuti Sakit KerajaanKERAJAAN
Arumalai0851921Cuti Sakit KerajaanKERAJAAN
Arumalai0851921Cuti Sakit PanelPANEL
Arumalai0851922Cuti Sakit PanelPANEL
Arumalai0851921Cuti Sakit PanelPANEL
Arumalai0851921Cuti Sakit PanelPANEL
Arumalai0851921Cuti Sakit PanelPANEL
Arumalai0851922Cuti Sakit PanelPANEL
Arumalai0851921Cuti Sakit PanelPANEL
Arumalai0851921Cuti Sakit PanelPANEL
Arumalai0851921Cuti Sakit PanelPANEL
Arumalai0851921Cuti Sakit PanelPANEL
Arumalai0851921Cuti Sakit PanelPANEL
Arumalai0851921Cuti Sakit PanelPANEL
Arumalai0851921Cuti Sakit PanelPANEL

<colgroup><col><col><col><col span="4"></colgroup><tbody>
</tbody>
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,791
Messages
6,121,611
Members
449,038
Latest member
apwr

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top