[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Microsoft Office

65K

Solutions

41K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.

Share tech news, updates, or what's on your mind.

Sign up to Post

HI Guys

Hope you can help

Ive been trying to use SUMIF, SUM PRODUCT with FREQUENCY and MATCH to sum the quantities in stock locations but ignoring duplicate entries

I have column A and Column B - column A has the stock location which can be repeated several times with the unique stock quantity against the location in Column B

Ive tried creating a formula to sum the quantities in Column B but only for each unique location so for example, if in column A Stock Location PB12345 is repeated 6 times and has a quantiity of 500 - when the SUMIF runs through the the two columns, it only includes the sum of 500 from PB12345 once and not include the six times its repeated

Ive attached a sample file and the desired result in cell F1 - the formula im using shows how many times its repeated but not the sum ?

If you could help I would be very grateful

J
0
Rowby Goren Makes an Impact on Screen and Online
LVL 12
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

0
Excel Power Pivot - I have Excel power pivot. How do I load/add an excel worksheet and create the power pivot? How do I make the link to my source document?
0
Hello Experts,

I created an Excel workbook that functions as a log for certain events that occur at my place of work. It also uses the information from the log to make another tab ("Open vs. Closed Events") that shows the part # that the event occurred for and whether or not the event is currently open or closed. What I need is another tab called "Scheduling Report" that will look at the "Open vs. Closed Events" tab and pull only the part # from the OPEN events. I started a "scheduling report" tab and I can pull the information from the other tab into the scheduling report and make the open part #s show up, but there would be spaces between all of the open part #s where closed part #s are. I would like the scheduling report to list all of the part #s that have an open event from the "Open vs. Closed Events" tab in Column A6:A105 without leaving blank spaces between when there are closed events.

I attached the file that I have been working on and entered some data to try to help you understand what I need.

Any help is appreciated. Thank you.
SEW-3-Point-Control-Log.xlsx
0
Hey all, we received an Excel file that when we open it it gives us a 'Microsoft Excel cannot access the file ' '. the file being an external website. We are unable to get past this screen unless we disable external content.

Where do I find this URL inside the excel file? Ctrl + F and typing in http or part of the URL or the whole URL doesn't bring anythign back...and I don't see anything in the VBA.
0
Hello Experts:

I need some help with some data management (using Excel and Access).

Background on Excel File (incl. two tabs "Raw Data" and "Summary"):
- The "Raw Data" tab contains four columns [Incident Date], [Incident Time], [Age], [Gender]
- Also, for each of these four columns, I added an adjacent "Delta" column using an IF statement to compare two adjacent cells (per row).
- For example, for the [Date] colummn, in cell B2, I compare cell A2 with A3.  If date = 07/01 = 07/02, I output either "Same" or "Different".  In this case, cell B2 returns "Different".
- Alternatively, in cell B3, I compare cell A3 with A4.  If date = 07/02 = 07/02, I output either "Same" or "Different".  In this case, cell B2 returns "Same".
- The remaining formulae (in columns D, F, and H) follow the same principle as applied in column B.
- Now column I... it's using a nested IF formula.   I want to determine where columns A, C, E, G are all the "Same" (or "Different").
- In this case, out of 323 rows, 90 rows are exactly the "Same" and 233 rows equal "Different" (see tab "Summary").

Background on Access file:

- I imported the Excel data from "Raw Data" (without the "Delta" columns).   Table "00_tblRawData_323_Records" includes 323 records.
- I created a Select/Make Table query ("00_qry_233_Records") which uses the "Group" feature in the query.  Upon executing the MakeTable query, it now creates table "01_tblRawData_233_Records".  
- The records in …
0
I wanted to migrate some data from one file to another one....

I have two excel files:  EE SAFE 1-12-18 and EE SAFE 1-18-18

On the EE SAFE 1-18-18 the orange column headers is what I want updated from another file EE SAFE 1-12-18 orange column headers it's data (columns O:V) on the MAIN_PN tab,
If there is a match.

I wanted to able to pick the previous file and update a current one with a button.  Please see attachments.
C--Users-lfreund-Desktop-EE-SAFE-1-.xlsm
C--Users-lfreund-Desktop-EE-SAFE-1-.xlsm
0
I have an Excel spreadsheet (about 50 rows), and am planning to create a nice Powerpoint template, with preformatted textboxes, fonts, etc.

I would like to transfer the information from the Excel into the PPT. Each row of the Excel will be one slide in the PPT. Each cell will become one text box. All cells in the same column will end up at the same place on various slides, with same font, etc. The final PPT file will contain as many slides as there were rows in the Excel spreadsheet.

Could someone please show me a way?
0
Hi Experts,

I am using  MS Query in Excel and I am trying to use a parameter (which is based on a cell value) in my case statement but I am getting a syntax error or access violation.

Select case when ? < value then do something

Open in new window


The ? mark represents the parameter value but obviously, I am using it wrong.

Thank you in advance!
0
I installed a new PC for the customer a few weeks ago.  They could not find their MS Office Home and Student 2010 CD or key, but that is the version they still wanted to use, so I installed a new Office Home and Student that I had bought on eBay.  Now, after almost 30 days, it keeps coming up saying it is not registered, and when I try to register it, it says it is a bogus product...which I guess is possible coming from eBay.

Meanwhile, they found the installation CD they owned with it's 25 digit key, so I tried to enter that key on the help screen, but it keeps coming up and saying that is an invalid key, advising that maybe it is for a different MS product (which it is not).   I even tried installing Office Home and Student 2010 on a test computer in my office, using their legit key, and it installed just fine.

So next I suggested that we uninstall Office 2010 and reboot their computer, and it did indeed uninstall it.  But when we go to reinstall, it never asks for the product key...it just goes along and installs the software again, apparently still using the bad key, because once installed it comes up with the same errors.

How can I get it to "completely" uninstall to a point where it will ask for the product key next time we install it from the CD?
0
Big Business Goals? Which KPIs Will Help You
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Hello everybody.

I've been dealing with a work that, unfortunately, will be in the hands of users with very poor expertise about Excel and pc.

The scenario is as follows. Every morning, before calling a macro (not discussed in this post) that produces a report, an user has to arrange some settings.
I have to "route" the user in the correct settings, exploiting dropdowns where it is possible and building a system of alerts highlighting incoherent choices.

As for me, I set up a VBA code in the sheet "Driver" - where the settings have to be arranged - consisting in a change event calling the macro update_driver at every change in the sheet.
Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A2:J51")) Is Nothing Then Call update_driver

End Sub

Open in new window


With the help of dropdowns built with Data Validation List, I've taken some steps forward, but there is always something to arrange. So, I was wondering if my way is the right one.

Put in practical terms: in column A the user has to type the city where the brenchs are located, and, like I've written, could modify them every day (also deleting some of them). For every brench in column A, a closed choice YES/NO has to be made in column B and C (of course same row).

In column E the user has to type - or delete if necessary  - the agent's surname, in column F has to type the agent's code, and in column G has to make a closed choice YES/NO.
As you can see in attachment dd2, …
0
I am trying to add an exchange profile to Microsoft Office 2016 (windows 10). It picks up the email address, however, when it is authenticating it asks for the credentials, but does not accept them and pops straight back up.

So far i have tried; both repairs for office, autodiscover reg keys, checking autodiscover on dns host, Adding a Disable WAM keys, uninstalling office, removing from domain, windows updates.

I have managed to add the profile to Windows 10 Mail and it works.
0
My requirement is to convert below pipe delimited  text (input.txt) file into an excel file (.XLSX) using VBScript .
 
Here is example of my text input file :-

input.txt (attached for reference)

Name|age|gender|department|year

don|23|male|computer|2010
ptalakes|24|female|maths |2015
chang|28|male|arts |2016
prag|29|female|science |2018
tom|26|male|arts |2017
john|27|female|sports |2018
simon|22|male|arts |2018

Output file  (output.xlsx attached )  should be saved in excel file as below in corresponding cell :-


Name      age      gender      department      year
don      23      male      computer      2010
ptalakes      24      female      maths      2015
chang      28      male      arts      2016
prag      29      female      science      2018
tom      26      male      arts      2017
john      27      female      sports      2018
simon      22      male      arts      2018



Would appreciate if some one could please help me to get complete code in vbscript.

Thanks in advance
Praveen
Input.txt
output.xlsx
0
I have a shared mailbox in Office 365 that a user needs access to.  I'd like the mailbox to show up in their Outlook Client/OWA as an additional mailbox.  I know that if I give the user Full Access it will work, but I'd like them to only have editor access to the inbox, not full.  Is that even possible?  I've given them mail box folder permissions only on the inbox, but then the mailbox does not show up in Outlook/OWA.
0
Hello @Saqib Husain, Syed hello everyone,

I am refering to the genius solution of Saqib Husain, Syed concerning the stock calculation https://www.experts-exchange.com/questions/29096373/Stock-cost-error.html.
And I want to ask you how to modify the code in order to accept also the quantity as number with decimals; three decimal digits. for example 125,586.  The decimal symbol I have is my Excel software is","  that Saqib Husain, Syed had adapted the code for me.

Please find attached the file

Thank you
Draft1.xlsm
0
EXCEL ODBC Driver not found. OS Windows 7 Prof 64 Bit.
Please direct me to the download for both 32bit and 64 Bit

The connection string is
Provider=MSDASQL.1;Persist Security Info=False;Data Source=Excel Files;Initial Catalog=C:\Users\PC9\Downloads\CONTACTS & STOCKISTS..Internal,Supplier,Partner.xlsx
0
I would like to write an excel formula (index match?) to get the state for each area code in a list.  In other words, I have a list of records and each one has a phone number.  I already stripped the extraneous stuff and pulled out just the area codes.  There is a column of three-digit numbers now, e.g.
408
248
212
etc.

Now I would like to look each one up in a table such as the one at https://en.wikipedia.org/wiki/List_of_North_American_Numbering_Plan_area_codes#United_States

Step one in using index match is to get the match function to work.  I tried
=match(d3,b2:b52,0)
and this didn't work because column B CONTAINS the value in d3 -- the formula I wrote would have worked if the table had been constructed differently.
0
Hi All,

I need your assistance with Excel to remove duplicates.

I have an Excel sheet with hundreds of duplicate values similar to the table below.

TABLE_NAME      COLUMN_NAME
Table1      Id
Table1      Id
Table1      Id
Table1      Id
Table1      Id
Table1      Id
Table1      Id
Table1      Id
Table2      Id
Table2      Id
Table2      Id
Table2      Id
Table2      Id
Table2      Id

How I can remove duplicates based on the TABLE_NAME column so only one value is shown?

TABLE_NAME      COLUMN_NAME
Table1      Id
Table2      Id
Exchnage.PNG
0
Hello Experts.

I am office 365 illiterate, and not sure if this can be done.
We have an on-prem, 2010 Exchange server setup in a domain. We use Office 2013 on each user’s PC (not office 365). So everyone uses Outlook 2013 from there PC’s.
 
Some of our staff users have their Outlook 2013 setup to access from home using Outlook Anywhere (connecting to Exchange server using HTTP). All is good…….

My boss brings in his brand new laptop with “Office 365 Business” on it (says he got a monthly subscription), and want me to hook it up to our exchange server so he can get his email from home….

I’m thinking no problem, I will just setup like with Outlook Anywhere and we will be good!
(Wrong)

I have tried using the options for exchange, Outlook…..none let me use the Exchange server  (mail.yourdomaion.com), just get email address, and password, then it tries and tells me wrong username or password.

So here is my question….
Can you connect up Outlook 365 to our Exchange server (like Outlook Anywhere) or is there some special way you have to do it online?

If you can setup like “Outlook Anywhere” How is this process?

Thanks in Advance

    Fubr
0
Become a CompTIA Certified Healthcare IT Tech
LVL 12
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Windows 10 Professional comes loaded with click to run applications.  One of these is called " Microsoft Office Desktop Apps " …..    I've read several articles for using powershell to remove these.   I've however not been able to find the right one in the list to remove.

I want to remove these for any future users who login to the computer as well as any who currently have it as an option.  

Any help is greatly appreciated.

Joel
0
Hi, everybody,

I have an Excel worksheet that holds a list of DB columns from a particular table.  Because there are so many columns in this table (529), I have a piece of VBA code that allows a user to put a "y" in column H in the worksheet for each table column that they want to return in their query.  The code then writes each of these column names into a string variable that produces the necessary Power Query M code to update their DB query.  It writes this code to a text file so that the user could copy and paste it into Power Query to run the new DB request.

The problem is that when it writes to the text file, it adds extra quotations that the user would have to remove.  For example, if I select the first 3 columns in the table and run the code, it produces the following:

"let
Source = Salesforce.Data(){[Name=""Booking""]}[Data],
Table.SelectColumns(Source, {""""Id"", ""OwnerId"", ""Name"", })
in
SelectColumns"

when what the user would need to copy into Power Query would actually be the following:

let
Source = Salesforce.Data(){[Name="Booking"]}[Data],
Table.SelectColumns(Source, {"Id", "OwnerId", "Name", })
in
SelectColumns

Is there a better way to do this in order to eliminate the extra quotations?  The VBA code is below.  Thank you for your help!
- doctornick0

Public Sub GenerateMQueryStringForSalesforce()
'Purpose:  The Booking table in Salesforce is too large to be returned with a Select * query via the SF object
'in order to 

Open in new window

0
I have an Excel formula;

=SUMIFS(InputData!$h$2:$h$999,InputData!$C$2:$C$999,"New Application",InputData!$L$2:$L$999,"=")
sum all numbers from column H where category (column C)=New Application for all dates from column L


screen shoot
it is working fine
I want to modify the sentence and use the function (Now()-1: InputData!$L$2:$L$999 should be the yesterday date
0
0
We're noticing that more and more of our users are having problems with appointments actively syncing between each other when using Outlook. We need a scheduled appointment to show up immediately, so duplicates are not created. We've taken it to disabling Outlook email caching on each computer so they are all actively syncing updates with the server. Since we've started this there hasn't been any issues until now, but now more and more users are starting to complain about things not showing up fast enough or right away.

We need to find a way to solve or implement a change to fix this problem. It’s odd that it is all of a sudden happening to numerous people. Is the only solution really for me to go to all computers and turn off cache mode? Or is there a bigger issue that could solve this?
0
0

Microsoft Office

65K

Solutions

41K

Contributors

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.