I have two big lists of contacts with job titles, I am looking to use the same filter criteria from one column on sheet 1 to the same column on sheet 2. It will take days to go through and select each specific job title so l was just curious if there was a way to copy the filter criteria to another sheet? Thank you!

This is a post I found from 6 yrs ago that hasn’t been solved to my knowledge and I’m having the same issue. Can anybody help with this ?

I need to lookup the latest price from a list of items. Can someone please suggest a formula to accomplish this?

Hey everyone! I'm trying to learn how to get Google Sheets to compare two sheets and combine them in a specific way. Any help would be appreciated.

PROBLEM DESCRIPTION:

1. My job requires me to run a report every week that produces an Excel sheet.

2. This sheet always has the same columns when I pull it but new roles are added when new clients are added so the rows consistently change.

3. I always add one column to new weekly spreadsheet when I pull it that is labeled "Priority". I then label each client as High, Low, and if I haven't gotten to it yet, it will be blank.

4. Every week I have to compare the sheet from last week (i.e. Last Week's Sheet) with that added to the new one (i.e. New Week's Sheet) that typically will have more or less row additions.

GOAL

I want to be able to combine the two sheets (Last Week and New Week) into a third sheet that combines and leaves out certain information.

1. I don't want to have double entries in the new sheet, just the information from Last Weeks Sheet if nothing has changed (e.g. if I have a row for Mr. John Smith and his details in Last Week's Sheet and he is also in the New Week Sheet because I haven't finished with him, I don't want to have two Mr. John Smiths with the same details in the new combined sheet.)

2. I want the new sheet to have the Priority column that I added in the Last Week sheet and the data in those cells and have that data correspond with the right person (e.g. if I took the time to add a priority column and listed John Smith as high priority, I don't want to have to add that column and write down that John Smith is high priority and the rest of those rows because there's hundreds).

--

Please let me know if any of this makes sense and if you have any questions. Any help would be amazing. Thank you, thank you, thank you.

I have made a video demonstrating my problem, but perhaps my method is not the best way of incorporating an animated gif. Just need a means to an end!

https://www.youtube.com/watch?v=LFltyIBXCGc

Hi Reddit -

I am attempting to transform this data:

|| || |ID Number|Name| |12341|Name 1| |12342|Name 1| |12343|Name 1| |12344|Name 1| |12345|Name 1, Name 4| |12346|Name 1, Name 4| |12347|Name 1, Name 2| |12348|Name 1, Name 2| |12349|Name 1, Name 2| |12350|Name 1, Name 2, Name 3, Name 5|

Into this format:

|| || |ID Numbers| Name| |12341|Name 1| |12342|Name 1| |12343|Name 1| |12344|Name 1| |12345|Name 1| |12346|Name 1| |12347|Name 1| |12348|Name 1| |12349|Name 1| |12350|Name 1| |12347|Name 2| |12348|Name 2| |12349|Name 2| |12350|Name 2| |12350|Name 3| |12345|Name 4| |12346|Name 4| |12350|Name 5|

Any ideas how I could accomplish this with some formulas?

Hi Reddit -

I am attempting to transform this data:

|| || |ID Number|Name| |12341|Name 1| |12342|Name 1| |12343|Name 1| |12344|Name 1| |12345|Name 1, Name 4| |12346|Name 1, Name 4| |12347|Name 1, Name 2| |12348|Name 1, Name 2| |12349|Name 1, Name 2| |12350|Name 1, Name 2, Name 3, Name 5|

Into this format:

|| || |ID Numbers| Name| |12341|Name 1| |12342|Name 1| |12343|Name 1| |12344|Name 1| |12345|Name 1| |12346|Name 1| |12347|Name 1| |12348|Name 1| |12349|Name 1| |12350|Name 1| |12347|Name 2| |12348|Name 2| |12349|Name 2| |12350|Name 2| |12350|Name 3| |12345|Name 4| |12346|Name 4| |12350|Name 5|

Any ideas how I could accomplish this with some formulas?

I have an XY table in excel. I would like to find the maximum Y value in a given X range. Example data given below. What equation can I use, in an unrelated cell, to output the max X value in between the Y range 2:8 (Result will return by 27).

Note: I'm using Excel 2013, which does not have the MAXIFS function, so please help me solve this problem without it.

https://preview.redd.it/1rzlhwzt2m0d1.png?width=285&format=png&auto=webp&s=d5ed8750bfb669254b94fb8a0355311be24077b9

So I’ve basically been given a project wherein I have to copy/paste an applicant’s name into a filter from one sheet in a file and find that same name in the other sheet, and find a user id number that I will then copy/paste into the first sheet. Is there any way to write a macro that will do this for me ? It’s time-consuming data entry intern work which I get, but there are 1200 names to go thru so I would like to slim the process down. Thank you if anyone can help.

Does anyone have a solution for calculating a weighted average, where it doesn’t take zeros into account on the calculation?

I’ve found weighted average formulas using =SUMPRODUCT(X:X,X:X)/SUM(X:X)

But this gives me lower than expected results because any zeros in my data are bringing down my numbers.

I’m trying to get column D to search for a type of cable, such as 5pin, and have N3 total the lengths I type into columns F through I.

So D3 would say 5pin, F3 would say 5’ in it, G3 would have 5’, I would like N3 to total that as 2.

I first tried =COUNTIFS(D,”5pin”,F:I,”5’”) That yelled at me.

Then I tried =COUNTIF(D,”5pin”,F,”5’”) and I still couldn’t get it to work for one column.

I’m new to these kinds of formulas so I’d love to have someone point out my mistake.

Hello,

I have a client that utilizes an excel spreadsheet to track equipment statues during construction, example below.

https://preview.redd.it/1hl82x22wl0d1.png?width=1445&format=png&auto=webp&s=383d6aa846ff17f7b4b646d91cac9960f601fac3

Each equipment gets the same kinds of steps, and the steps generally have to happen sequentially from left to right, IE Red Tag needs to be complete before MEP Test can begin. For reference, the project that this is coming from has 27 unique steps for close to 1,500 pieces of equipment.

I want to take this template and create a schedule that will show when I want each step to be started and completed, and put that into some sort of Gant format for tracking purposes. I feel like I have to somehow reorganize the table for that it formats into a schedule nicely, but there may be an easier approach. I'm sure there's not a conventional way to organize that with the way the tracking sheet is set up, but if anyone has an idea or two on how to make that work, I would really appreciate the suggestions!

Okay so, I cannot run commands suddenly in my excel document. I am making a budget and have previously (in the same document) been able to run basic commands like summations and subtractions, but now when I try to divide two numbers to get a percentage to include in my pie chart, I get zero. For any command that I run from this table that is linked to other parts of the sheet, I get zero. I have attached an image showing the command I am using and I get zero when I divide two numbers where the answer is not zero.

I love excel but why is it so quirky?

TIA

I get zero for this command

Novice Excel user here: Currently working on a sales report template and I’m not able to calculate the sales (in $) from LY.

Here’s the data I currently have :

Sales from this year ($) : Column A LY variation (%) : Column B

Example:

This year sale for SKU X : $8742.92 Variation from last year for the same SKU: 113.72%

For other SKUS, I also have variation in negative. (ex: -83.33%)

What formula should I use?

Thank you for your help! :) I’m available if you have any questions.

Hi r/excel I was wondering if there was anyway to split the 'Money Out' and 'Money In' columns into separate tables. This would make my life a millions times easier so I appreciate any help or advice you have for me :)

https://imgur.com/a/FUIQwZm

Essentially i'm tracking information and want to make it so that the cell that = 1 triggers a printable version ie. groups certain columns i don't need to see. I've tried googling and haven't found anything.

I used =IFERROR and it's working fine but I have 2 different errors and I need 2 different results

If it's a dividion error I want my formula to return 0

If it's a name error I wan my formula to return empty cell or ""

More info:

It's part of report and I divide SLA by monthly runtime average (how fast tickets are closed) to get percentage of requests closed on time, e.g. SLA 3 days, runtime 4 days, 3/4=75%

Sometimes runtime is 0 (because we didn't have any tickets in that category) so I get division error but I want to see 0.

Also for upcoming months we don't have runtime data yet and the cell is empty so I get name error, but I want to see blank cell.

Hi gang,

I have groups of 3 cells (H36-H38) in a column that all need to reference the same cell (C18).

Easy, fine.

But then I need the next 3 (H39-H41) cells to reference C19. I can’t seem to get excel to autofill the rest of the H column.

I’m building a database of items with different variations and don’t have access to a data entry form on my version of excel. Any help would be greatly appreciated!

Thanks!

https://preview.redd.it/9nvpy4r8rl0d1.png?width=1381&format=png&auto=webp&s=0a836e1b5f7d1d2005f71fee74a83e2c7a8d5cb9

This might be an easy fix and I might be in the right direction, but basically what I'm trying to do is, if the equipment called Isolator requires a UR, IOQ, IQOQ and OJT, then the table below will populate with those items to be generated. I've had some luck using Xlookup and Filter, but I was wondering if you guys had any easier way to go about it. I'm happy to share the formulas if you want to discuss further. Thank you!

As the title states, I have a bar graph and the left side of the y axis has my values but the ride y axis is blank. I was able to get tick marks added to the labeled right side but I need corresponding tick marks that line up on the right as well. Is this possible? Thank you

Each week, our appointment system will generate a list of customers who did not attend their appointments. We have a policy in place that the customer will receive a warning text after missing 1 appointment, a follow-up text after missing 2 within 6 months, and a formal letter after 3 within a year. However, the current system is very manual. I want to put that weekly list that gets generated into a spreadsheet, then each week, when I paste in the new weekly data, have the spreadsheet flag if that customer has previously missed an appointment and update to show how many appointments have been missed within the current data set.

I hope that makes sense.

I have recently migrated an Excel database to MySQL and am having some issues when using a formula like VLOOKUP when my lookup value is ONLY a number but works when the lookup value contains both numbers and letters (which occurs often).

For context, this is (part of) my SQL file, which is then loaded into Excel via OCDB and imported into a table with PowerQuery.

CREATE DATABASE IF NOT EXISTS ML;
USE ML;

CREATE TABLE IF NOT EXISTS MasterList (
    `FINISHED_PN` TEXT,
    `DESCRIPTION` TEXT
);

LOAD DATA INFILE 'MasterList.csv'
INTO TABLE MasterList
FIELDS TERMINATED BY ','
ENCLOSED BY '\''
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

TEXT datatypes in the .csv are enclosed by single quotes (apostrophe), with each line separated by a comma.

And then in a separate sheet, I use VLOOKUP to get the corresponding description of a part number:

The first VLOOKUP corresponding to C25 returns #N/A, while the next VLOOKUP corresponding to C26 returns the correct description. Through some testing, I've found it only occurs for part numbers that contain ONLY numbers.

The workarounds I've tried do work but on the Excel side, meaning I'll have to modify the data or formulas in Excel, rather than maybe importing the data from MySQL in a different manner.

1. =VLOOKUP(TRIM(C25),TRIM(masterlist),3,FALSE) was one solution that worked, but it would mean that I'd have to modify the VLOOKUP formula on every sheet.
2. "Modify" the 'masterlist' table in Excel by clicking on the textbox of a FINISHED_PN and then just hitting enter without making any changes. Then, the formula I use works but is reset when refreshing the connection.
3. Adding a data validation list of the FINISHED_PN column to the cells in other sheets (my current solution)

Is there a better solution to making this external database work how I want with Excel? ie. no additional changes other than import the table and go. I have full access to both the MySQL database and Excel, so I'm open to anything.

I'd appreciate any help!

So I have a table of names and I want to put them into groups of 3 or 4 (4 preferred) I used =rand then =rank to gain a number to be divided by my group size then used math.ceiling to round it and provide the effective group number. Issue is obviously with say 26 names it will create 6 groups of 4 and a group of 2 is there a way to make it 5 groups of 4 and 2 groups of 3?

I have one tab that is accruing information from ytd, and one that has all of our sales from last year. It is divided into total food sales and total restaurant sales. We have a lot of variables that influence our rate of sales such as weather or events going on in town. I have tried using this function: =FORECAST.ETS(B2,Table4[[Total Food ]],Table4[Date],1). The numbers I am receiving from this are not terribly far off, but far enough for it to not be very accurate.

What is the best way you would do this? It would be cool to have drop downs for different weather conditions, "busy weekend / not busy" etc. I am just not sure on how to do this!

I'm trying to automate the creation of some lists based on data entered in a separate table. Looking at the attached image, we have a table with some cue numbers, instruments, and a number corresponding to how many parts the instrument has on the cue (in this case, either 0, 1, or 2, but the values could be higher).

Underneath this table is a summarized list of which instrument has a part on which cue. The extra kicker though is that if something has more than one part, I'd like that listed as well. Is this possible? I essentially want to know if there's a way to automatically and dynamically create the list shown on the bottom (B29:E43) using the table on top (A1:E25).

Edit: here's a link to the spreadsheet itself https://www.dropbox.com/scl/fi/aanfpcuvzyd5lgke1p29p/Auto-List-Sample.xlsx?rlkey=1xocbnaw0ky8m86emo6s2fszf&dl=0

https://preview.redd.it/gtos2bhlnl0d1.png?width=972&format=png&auto=webp&s=ec5fad0165af29afa32885e48b7fdbcc1bcc260e

Hello, I’m trying to save or move my Excel file to my desktop or my downloads, but neither one is an option for me. The only choices I have are to save my file to my OneDrive or to my folders. I’m trying to figure out how do I save it to my desktop or to my downloads. Thank you

I use conditional formatting to check if any of the values in cells U1, X1, AA1, and AC1 are less than L1, N1, P1, and R1, respectively. B1 is then highlighted if this condition is true and B1 is not empty.
Formula used: =AND(OR($U1<L1, $X1<$N1, $AA1<$P1, $AC1<$R1),B1<>"")

In my spreadsheet, U1 = 40 and L1 = 40 yet B1 gets highlighted. I've isolated it to issue to this two cells. Both are already in number format without decimal spaces.

Note: L1 came from this formula = =((T2-18.81)/5.625)*10+50 (this formula measures the norms and is unchangeable)

T= 13

I've tried the ff:
-adjusting the comparison operator to include = (such as <=)
-ensuring consistent formatting: number, zero decimal
-using roundup, rounddown etc
-using helper columns

Would appreciate any help guys. I've been at this for hoursss

https://preview.redd.it/mtggbn1cll0d1.png?width=2074&format=png&auto=webp&s=bb377284ba61fc1f4aa448b924dae5b053034c7c

I use Excel on Mac.

Hi Guys,

I have a question. I have an image folder with 500 photos and would like to create a dynamic formula for my excel file. In theory, If the filename contains the text in excel cell then it would return an image. Is this possible? Please help 😁

=IF(AK56="Pending Install Date","Pending Install Date",IFERROR(HLOOKUP("Greater Than 10 Weeks",AL56:AO56,1,FALSE),(HLOOKUP("Below 6 Weeks",AL56:AO56,1,FALSE))))

In column AS, I have been able to get the formula to return 3/5 variations. Need assistance on how I could get the formula to include "Between 6 to 8 Weeks" & "Between 8 to 10 Weeks".

https://preview.redd.it/hgv2h3tnfl0d1.png?width=1231&format=png&auto=webp&s=b6bf87a888bb9e5fa2aa6c1daf0096bb9a92ce18

I’m trying to insert 3 rows between data on a spreadsheet with about 100 rows of data. I want to add the rows between each fiscal year. This is how far I’ve been able to get:

Sub InsertRowswithSpecificValue()

Dim cell As Range

For Each cell In Range("b2:b101") If cell.Value = "2019" Then cell.Offset(3).EntireRow.Insert End If Next cell

End Sub

From here, how can I make my “if cell.value” separate say 2019 and 2020 and so on?

Hi all, I'm trying to bring in the value from a cell in one worksheet to another. My struggle is the cell in the source worksheet keeps changing. A checkbook register for example. I want to bring in the balance to another sheet but obviously the cell reference changes as new entries are added to the check register. Hope this makes sense. Thank you