Data to be divided in columns

bangushus

New Member
Joined
Jun 15, 2010
Messages
28
Hi!!
I have to calculate the number of flights operating each day,Now days of operation are mentioned in a single column as 01234567(Its a text)- which means that flight operates on all days except Monday.
I have break this data so, I add 7 more columns for each day from Monday to Sunday and put 1 as value in each column except that for monday.This in the end gives me in total how many flights are operating on each day of the week throughout the schedule.Now the data i have is hugh and i have to do this exercise for all the flights,Can there be any other way of doing it?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
How does 01234567 signify all days except Monday?
What would it be to signiy all days except Wednesday?
 
Upvote 0
Sorry!!
For the flight which does not operate on Monday,the schedule would mention as 0234567 for a flight which operates on all days except saturday,it denotes 1234507
The idea is that if you've been given a list of flights operating of different day frequency,how would you calculate unless you are able to calculate ,how many flights operate on monday and so far?
 
Upvote 0
presently i work as following
<TABLE style="WIDTH: 660pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=880><COLGROUP><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" span=11 width=80><TBODY><TR style="HEIGHT: 55.5pt; mso-height-source: userset" height=74><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 60pt; HEIGHT: 55.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=74 width=80>Airline (Arrival)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=80>Airline (Departure)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=80>Flight No. (Arrival)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=80>Days of Operation</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=80>MON</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=80>TUE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=80>WED</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=80>THU</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=80>FRI</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=80>SAT</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 60pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 width=80>SUN</TD></TR><TR style="HEIGHT: 16.5pt; mso-height-source: userset" height=22><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 16.5pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=22>LI</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>LI</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71>151</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl73>0234560</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl70></TD></TR></TBODY></TABLE>
 
Upvote 0
Are you using 2007? If so, try:

=IFERROR(IF(SEARCH(COLUMNS($B$2:B2)-1,$D2)>0,1,""),"")

Replace $D2 with the first cell containing days of operation and drag this formula across all your new day columns. You should then be able to just sum each day.
 
Last edited:
Upvote 0
Assuming that the cell with Airline (Arrival) is cell A1 (message #5) then in E2:
=IF(ISERROR(FIND(1,$D2)),0,1)

in F2:
=IF(ISERROR(FIND(2,$D2)),0,1)

in G2:
=IF(ISERROR(FIND(3,$D2)),0,1)

in H2:
=IF(ISERROR(FIND(4,$D2)),0,1)

in I2:
=IF(ISERROR(FIND(5,$D2)),0,1)

in J2:
=IF(ISERROR(FIND(6,$D2)),0,1)

in K2:
=IF(ISERROR(FIND(7,$D2)),0,1)

The only difference being the red highlights.
Now select E2:K2 and copy down as far as needed.
This means that column D can be
123
13254
and it will still work, and the number can be a real number, because it doesn't need to see leading zeroes so:
0004567 and 4567 will yield the same result
0230060 and 236 will yield the same result.

The numbers don't even need to be in the correct order:
32100
will still give 1 for Mon, Tue, Wed.
 
Last edited:
Upvote 0
IFERROR only works if you have 2007 or later, so you're fine with 2010.

I made a mistake on the column B part, I meant to put COLUMNS instead of ROWS, but the idea is that with p45cal's function you have to manually type in the number on each column.

So you can get around that by using COLUMNS instead, all it does is count the number of columns in that range. Because it will never go below 1 since COLUMNS($B$2:B2) is still one column, I put in the -1.

Anyway, is sounds like P45cal's formula worked for you, it's a pretty clever approach. FIND and SEARCH give you numeric result of where in the cell they found the first instance of you're looking for and then return an error if they can't find it at all, but since you don't really care about where in the cell the value is, just whether it exists at all, his ISERROR approach works perfectly.

That being said, if you hadn't already typed in all those day numbers manually, you could do this:

=IF(ISERROR(FIND(COLUMNS($A$1:A$1,$D2)-1),0,1)

and then just drag the formula across all the cells. Not that you need to, but FYI. :)
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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