microsoft excel – Pivot table data filter showing unused dates

Normally when I create a pivot table and filter on a date field, it only shows me dates which are in my data – this is useful and relevant because my data might only come through weekly for example.
I’ve just changed the data source on a pivot table that was behaving just so, from a power query to a connection with direct SQL (not sure what this is officially called). The data is from the same collection of dates, but now when I access the date filter I see every date in the year displayed, as well as a <date and at the end a >date

Why has it changed, and how can I set it back to only show valid dates from my data set?


for reference, the source dates are:

enter image description here

microsoft excel – I need a formula that if two values match from two different sheets, return a value from a third cell into another sheet cell

I am looking for a formula that matches the value in column E of Employee sheet with the same value in column A of the Vacation sheet, then taking the value in column B of the matching row on vacation schedule shee and entering that value into Column F of the Employee sheet. Another words, I want to take the employee’s years of service in column E of the employee sheet and match it to the years of service column in the vacation schedule sheet. Then take value in column B (Hours) of the vacation schedule and entering that number in column F(vacation hours) in the employee sheet.

web development – What framework to use with excel for data frame in javascript like pandas?

I am trying to create a website that displays values from a .csv file that matches a certain criteria similar to how you can filter data from a dataframe in the pandas framework in python. I am not sure if there is anything similar in javascript. If not, what framework should I use to do this using python for web development?

worksheet function – How to write an Excel Formula that takes a total across multiple repeating rows

I’m working with payroll data in a spreadsheet. What I’m looking to find is a total across different rows of employees with different roles. For example, how could I write a forumla for the “Total Hours” column below?

║ Emp # ║ Employee ║   Role    ║ Hours ║ Total Hours ║
║     1 ║ Jim      ║ Role 1    ║     5 ║           7 ║
║     1 ║ Jim      ║ Role 2    ║     2 ║             ║
║     2 ║ Bob      ║ Only Role ║    10 ║          10 ║
║     3 ║ Jane     ║ Role 1    ║    10 ║          15 ║
║     3 ║ Jane     ║ Role 2    ║     2 ║             ║
║     3 ║ Jane     ║ Role 3    ║     3 ║             ║

worksheet function – Plotting combined column charts in Excel

I have data like as shown below in csv file for 3 terms such as A, B, C.

I am trying to plot them using a column chart.

enter image description here

Basically what my data indicates (ex: Terms A) is out of 200 items, 150 belongs to the MP category and 50 belongs to the UMP category.

So orange and grey should be within a blue color. It shouldn’t be stacked one on top of another.

So, my graph should only have two colors because Total (200) should be overwritten by MP (150) and UMP (50)

ms office – Applescript adding email content to Excel

Greetings im trying to use the script created by Tim Vaughan and posted on this page.
The problem im having is that his format is:

Name: XXXX

and mine is:


code is

on main()
set mm to {}
tell application “Mail”
repeat with m in (get selection)
set mm’s end to m’s content & linefeed
end repeat
end tell
set r to my retrieve_data(mm)
do shell script “printf ‘%s’ ” & r’s quoted form & ” > ~/desktop/$(date +list
return r
end _main

on _retrieve_data(mm)
list mm : list of message text
script o
property pp : mm
property qq : {}
property rr : {}
property boundary : do shell script “uuidgen” without altering line endings — UUID & LF
property batch : 50 — number of messages to be processed at once; combined text should not exceed ca. 200K

    -- divide messages into batches
    repeat with i from 1 to count my pp by batch
        set j to i + batch - 1
        if j > (count my pp) then set j to -1
        set my qq's end to my pp's items i thru j
    end repeat

    -- retrieve data per batch
    repeat with q in my qq
        set my rr's end to do shell script "perl -CSDA -w <<'EOF' - " & boundary's quoted form & "

use strict;
local $ = qq(n);
local $, = qq(,);

my $boundary = shift;
my @data = ();
my ($new, $complete, $i, $j) = (1, 0, -1, 0);
while () {
next if ! ($new ||= $_ =~ /^$boundary$/) && $complete;
if ( $new ) {
($new, $complete) = (0, 0);

/^From:s*(.+?)s*$/o && do { $data($i){from} = $1; $j=0; next; };
/^Gender:s*(.+?)s*$/o && do { $data($i){gender} = $1; $j=0; next; };
/^Age:s*(.+?)s*$/o && do { $data($i){age} = $1; $j=0; next; };
/^Mobile:s*(.+?)s*$/o && do { $data($i){mobile} = $1; $j=0; next; };
/^Phone Day:s*(.+?)s*$/o && do { $data($i){phoneday} = $1; $j=0; next; };
/^Email:s*(.+?)s*$/o && do { $data($i){email} = $1; $j=0; next; };

/^Address:\s*(.+?)\s*$/o      && do { $data($i){address1} = $1; $j=1; next; };
$j == 1 && /^\s*(.+?)\s*$/o   && do { $data($i){address2} = $1; $j++; next; };
$j == 2 && /^\s*(.+?)\s*$/o   && do { $data($i){address3} = $1; $j++; next; };
$j == 3 && /^\s*(.+?)\s*$/o   && do { $data($i){address4} = $1; $j++; next; };

/^I would like to be kept up to date on future Eden events, competitions and special offerss*$/o
&& do { $data($i){eden} = ‘1’; $j=0; next; };
/^I would like to be kept up to date on future Kuoni events, competitions and special offerss*$/o
&& do { $data($i){kuoni} = ‘1’; $j=0; next; };

$complete = (0 + keys %{$data($i)} == 12);

my @keys = (‘from’,’gender’,’age’,’mobile’,’phoneday’,’email’,’address1′,’address2′,’address3′,’address4′,’eden’,’kuoni’);
print map { s/”/””/og; qq(“$_”) } @keys;

for (@data) {
print map { $_ = ” unless defined $; s/”/””/og; qq(“$“) } @{$_}{@keys};
” & _join(q, boundary) & ”
EOF” without altering line endings
end repeat

    -- combine data from each batch
    set r to my rr's item 1 -- include header
    repeat with i from 2 to count my rr -- exclude header for rest
        set r to r & my rr's item i's text from paragraph 2 to text -1
    end repeat
    return r
end script
tell o to run

end _retrieve_data

on _join(tt, d)
list tt : source list
string d : separator
return string : tt joined with d
local astid0, t
set {astid0, AppleScript’s text item delimiters} to {AppleScript’s text item delimiters, {} & d}
set t to “” & tt
set AppleScript’s text item delimiters to astid0
on error errs number errn
set AppleScript’s text item delimiters to astid0
error errs number errn
end try
return t
end _join

Thanks and regards

Luis Berrios

python – Load multiple worksheets from Excel file to multiple DataFrames

The spreadsheet can be found just above the references section at this link.

It’s a publication for STR DNA locus frequencies to calculate match probabilities. I’d like to build a match probability calculator and scale it up to do hundreds of thousands of calculations rather than just one. The first step is loading the frequencies into memory and the subject of this code. I figured a dataframe for each population set would be alright.

The first row in each worksheet is just a population label. It can be ignored.

The second row is the header: The first column “Allele” is the STR allele call. This is what will be compared to for matching. This will typically be an integer but sometimes can have a decimal of .1, .2, or .3 most commonly. Sometimes they are a string with a greater than or less than symbol (e.g.; “<9.2”, “>17”) although I don’t know if there are any in the excel file.

The 2nd through 25th columns are the different locations that are tested. Each is independent.

I will ignore anything after the 25th column and anything past the last row of frequencies. Most of the data points within these bounds are empty. They will be filled with what is called the minimum allele frequency. If you are interested in anything further I’d be happy to chat, but it’s probably not pertinent to the discussion at hand.

I’m sure there is a more elegant way to do this and probably a quicker way to do it. That’s why I am here. If there’s a better structure to put this in, or a faster/more elegant way to do it, please let me know. This is my first stab at it. The frames take about 3 seconds to load all the data. I’m guessing it should be much quicker than that. Anyway, here’s my code.

# -*- coding: utf-8 -*-
import pandas as pd

#create dataframes for population tables
caucasian_freq = pd.DataFrame()
swh_freq = pd.DataFrame()
seh_freq = pd.DataFrame()
agg_aa_freq = pd.DataFrame()
aa_freq = pd.DataFrame()
bah_freq = pd.DataFrame()
jam_freq = pd.DataFrame()
trin_freq = pd.DataFrame()
cham_freq = pd.DataFrame()
fili_freq = pd.DataFrame()
apa_freq = pd.DataFrame()
nav_freq = pd.DataFrame()

#get the file name 
expanded_frequencies = '1-s2.0-S1872497316301429-mmc1.xlsx'

#for the number of columns containing frequencies
data_columns = (0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25)
#for minimum allele frequency 
min_freq = (0.0124, 0.0120, 0.0095, 0.0046, 0.012, 0.0159, 0.0141, 0.0316, 0.0263, 0.0550, 0.0130, 0.0175)

#import the different freq sets - ignore the first row, don't read anything past column 25
caucasian_freq = pd.read_excel(expanded_frequencies, "Caucasian", header=1, usecols= data_columns)
swh_freq = pd.read_excel(expanded_frequencies, "SW Hispanic", header=1, usecols= data_columns)
seh_freq = pd.read_excel(expanded_frequencies, "SE Hispanic", header=1, usecols= data_columns)
agg_aa_freq = pd.read_excel(expanded_frequencies, "African Amer Bahamian Jamaican", header=1, usecols= data_columns)
aa_freq = pd.read_excel(expanded_frequencies, "African American", header=1, usecols= data_columns)
bah_freq = pd.read_excel(expanded_frequencies, "Bahamian", header=1, usecols= data_columns)
jam_freq = pd.read_excel(expanded_frequencies, "Jamaican", header=1, usecols= data_columns)
trin_freq = pd.read_excel(expanded_frequencies, "Trinidadian", header=1, usecols= data_columns)
cham_freq = pd.read_excel(expanded_frequencies, "Chamorro", header=1, usecols= data_columns)
fili_freq = pd.read_excel(expanded_frequencies, "Filipino", header=1, usecols= data_columns)
apa_freq = pd.read_excel(expanded_frequencies, "Apache", header=1, usecols= data_columns)
nav_freq = pd.read_excel(expanded_frequencies, "Navajo", header=1, usecols= data_columns)

#truncate rows without data - the row after the last row of data is a duplicate of 
#the header row. Use it to find the last row of data
caucasian_freq = caucasian_freq.truncate(after=caucasian_freq.loc(caucasian_freq('Allele') == 'Allele').index(0)-1)
swh_freq = swh_freq.truncate(after=swh_freq.loc(swh_freq('Allele') == 'Allele').index(0)-1)
seh_freq = seh_freq.truncate(after=seh_freq.loc(seh_freq('Allele') == 'Allele').index(0)-1)
agg_aa_freq = agg_aa_freq.truncate(after=agg_aa_freq.loc(agg_aa_freq('Allele') == 'Allele').index(0)-1)
aa_freq = aa_freq.truncate(after=aa_freq.loc(aa_freq('Allele') == 'Allele').index(0)-1)
bah_freq = bah_freq.truncate(after=bah_freq.loc(bah_freq('Allele') == 'Allele').index(0)-1)
jam_freq = jam_freq.truncate(after=jam_freq.loc(jam_freq('Allele') == 'Allele').index(0)-1)
trin_freq = trin_freq.truncate(after=trin_freq.loc(trin_freq('Allele') == 'Allele').index(0)-1)
cham_freq = cham_freq.truncate(after=cham_freq.loc(cham_freq('Allele') == 'Allele').index(0)-1)
fili_freq = fili_freq.truncate(after=fili_freq.loc(fili_freq('Allele') == 'Allele').index(0)-1)
apa_freq = apa_freq.truncate(after=apa_freq.loc(apa_freq('Allele') == 'Allele').index(0)-1)
nav_freq = nav_freq.truncate(after=nav_freq.loc(nav_freq('Allele') == 'Allele').index(0)-1)

#fill in nas with the minimum allele frequency
caucasian_freq.fillna(min_freq(0), inplace = True)
swh_freq.fillna(min_freq(1), inplace = True)
seh_freq.fillna(min_freq(2), inplace = True)
agg_aa_freq.fillna(min_freq(3), inplace = True)
aa_freq.fillna(min_freq(4), inplace = True)
bah_freq.fillna(min_freq(5), inplace = True)
jam_freq.fillna(min_freq(6), inplace = True)
trin_freq.fillna(min_freq(7), inplace = True)
cham_freq.fillna(min_freq(8), inplace = True)
fili_freq.fillna(min_freq(9), inplace = True)
apa_freq.fillna(min_freq(10), inplace = True)
nav_freq.fillna(min_freq(11), inplace = True)

I will do data entry work, PDF to word or excel for $2

I will do data entry work, PDF to word or excel

I am an individual Data Entry freelancer aiming towards providing quality services.
I will be your Virtual Assistant and provide you with any type of Data Entry, Data Mining, Web Research or Copy Paste services!!

I have worked for many websites and freelancing individuals and helped them with Administrative support.

During my tenure as a Data entry operator, I gained a firm grasp of the following:

  • Uploading products on e-commerce site

You can have complete faith in me and trust me with your work. I assure you one time delivery of work with a quality output.


Help with excel error – “Removed records: Workbook properties”

Can someone help with this please? I spent so many hours without finding an answer…

I have an “.xlsm” excel file from which I need to have some sheets deleted (but keeping the original file as-is). I am able to copy the file into a new one and have the un-needed sheets deleted, but when I try to open it I get the following excel message:

“We found a problem with some content in “Worbook1.xlsm”. Do you want
us to try to recover as much as we can? If you trust the source of
this workbook, click Yes”

When I click “Yes”, the repair message is: “Removed records: Workbook properties from /xl/workbook.xml.part (Workbook)”

I checked on the following things so far, really not sure what else it can be:

  • All tab names are less than 31 chars
  • All tab names do not have strange chars like ‘ or /. Rather they are named like “Reporting Tab”
  • I also tried saving it down as “.xlsx” but didn’t even let me open the file

To give more context, the file is saved in a shared drive, and I am copying the new file in that same shared drive. Any idea for me to try will be appreciated.

I will be Virtual Assistant and All Kinds of Data Entry Works For You with 24/7 Support for $5

I will be Virtual Assistant and All Kinds of Data Entry Works For You with 24/7 Support

Thanks for checking my service ,

Do you need a reliable and professional virtual assistant , Data Entry, Web Research or List Building Projects using MS Excel? Yes, you are at the right place. Please check out my expertise below.

Here are the services I offer:

  • Data Entry 
  • Data Mining
  • Excel Data Entry
  • CRM Data Entry
  • Data Scraping
  • Copy Paste Work
  • Data Conversion
  • PDF to Excel 
  •  JPEG to Excel 
  •  word Typing in Excel 
  •  Shopify Data Entry
  • WordPress Data Entry
  • E-commerce Products Listing
  • Web Research and 
  • Web Scrapping
  • Data Collection from LinkedIn/Instagram
  • Property Research, Public Record Search
  • Any type of Excel task

Note:  Please contactme first before placing an order to check availability and price estimate of

your project.
Why should you choose me? 
100% Satisfaction

✓ Complete Data Security

✓ 100% Accuracy

✓ Quality Assurance

✓ On-Time Delivery