This is very time sensitive to please help a girl out! I need to merge two versions of a workbook that was filled out by two different people. But I am not being able to copy and paste from one workbook to another without wiping our the data of the second workbook. I can't find any options to merge workbooks on my mac. Please help!

Hi there,

I'm working on a final project for a class where I have to analyze data for a fictional streaming service. One of the questions I have to answer is "Which genres and shows are increasing or decreasing in popularity?"

I thought that I could use the SUMIF formula here to add the number of users watching each genre, but each time I try to use the SUMIF formula, I get 0 as an answer, which I know is not correct.

https://preview.redd.it/0slt5mofvx6e1.png?width=2018&format=png&auto=webp&s=b49d3278dcdd30168dd9e8ac6db6ee0882b5d60f

I'm not sure what I'm doing wrong.

Hello,

I'm working on a final project for a class where I am analyzing data for a fictional streaming service. I am required to include graphs in my final presentation.

I am attempting to add a graph that shows which genres and shows see the highest or lowest time spent per user. When I try to create a graph including the three criteria (month, total time spent, and genre), I get the following graph:

https://preview.redd.it/8lx7k5gjux6e1.png?width=2440&format=png&auto=webp&s=8f1df7a12de70a58126726b2019c6e9c0479583f

I did select the full month column when selecting data, but for some reason, it defaults to only July 2024 after I create the graph. As you can see, it also isn't showing any data on the graph, and the time spent and genre are just horizontal axis labels.

How do I fix this problem?

Hello,

I am trying to have cells on a separate page reference cell values that lie within an area that will be filled in by Google Form responses.

Whenever a new response is added, however, the cells no longer reference, for instance, ={'Form Responses 1'!B$3}, but ={'Form Responses 1'!B$4} - moving one down.

I have tried absolute values, and that has not worked.

I would prefer to reference individual cells, as the pages the form responses are being copied to are set up a bit differently.

Any help would be immensely appreciated at this final hour,

thankyou.

Hello

wish i could upload an example but i m on mobile and just had it in my mind since friday,

i have a sumifs which matches names, dates and another parameter from another file and bring the hours. this list contains the same names couple of times so i want the sumif formula to bring the result only once when it matches, how can it be done?

I have a MS form that is populating a table in excel. That table in excel is being unpivoted in power query and loaded back in excel.

Currently when new forms are submitted the only way I can update the power queried table is by manually refreshing.

Is there anything I can do to have this auto refresh?

I have data on a tab that is referenced from another tab using if formula. There is about 360 rows. So only certain rows get pulled in. Issue is they are everywhere. How do I pull the ones that show up to the top for easy printing?

How can I make the start date (left) populate the Calander table (right) I made automatically?

https://preview.redd.it/inqb6vy2hw6e1.png?width=1386&format=png&auto=webp&s=c93b41c9afa08b6032a73c00d5f8c365bf2e962e

This must be a simple thing, but I just cant wrap my head around it.. I either need help with P34's formula or the averages in L.

The numbers in B3:K10 represent the number of guests that gave that particular score to the question on the left. Link to workbook

I'm trying to analyse properties and predictability of square numbers. It results in repeating patterns from the integers in sequences. I want to compute a formula which can identify repeating patterns within the scope of the sequence:

(In the image I have only included visuals of up to row 21 as to not make the text unreadable while retaining conciseness; formulae are as follows:
In A4: =SEQUENCE(B1)

In B4: =A4#^2

In C4: =MOD(B4#,10)

In D4: =INT(MOD(B4#/10, 10))

)

https://preview.redd.it/pm1gvytcfw6e1.png?width=255&format=png&auto=webp&s=aa399abff7f08d94cbdf4218875c94d851736428

https://preview.redd.it/zrkvnwbkfw6e1.png?width=553&format=png&auto=webp&s=481d4867730c32fdf4040bc684dc4c869f47ebd7

I’m trying to round my decimals to a fraction where the numerator is always 1.

For example:

0.0450 = 4/89 ~ 1/22 0.0483 = 3/62 ~ 1/21 0.0700 = 4/57 ~ 1/14 Etc., etc.

Can’t seem to find an answer here, or maybe I’m misunderstanding what I need to do. I feel like this should be an easy one but I can’t figure it out. Any help is appreciated.

I work as a “spend consultant”, helping clients to negotiate large software contracts. I’ve built a template that I’m generally very happy with, yet there is one little nit that drives me nuts.

The template has a minimum of ten, but up to 35-40 , identical tabs all with different scenario names (usually dates). Each tab provides line item detail about a price quote from a vendor. The layout is the same between those (up to) 40 tabs. Same field names, just the data is different. There are usually multiple years of data included on each tab. Sometimes one year, sometimes three, five, seven, etc. These individual tabs can have 10, 20, 30 rows, or several thousand rows. It just depends upon the complexity of the deal.

As some point in the process, I’ll manually copy each row of the individual tabs to a sheet I call “consolidated” and then I’ll generate a pivot table off that data. This is useful to enable me to more obviously see the differences in very complex quotes. Each line has sufficient data to enable me to understand the date, the year, the scenario, and more. It really screams “use a pivot table” to “make order of rows and columns of data”. I am able to usually turn the 35-40 tabs into a single summary page. The vendors usually only change a fraction of the line items between quotes and for various scenarios. I just build a pivot table that shows the structure of the deal and the data is, obviously, different for each scenario/date/etc. It makes it very easy to spot the difference and to tell the story about our progress (or lack of progress) in the negotiation. It also makes me look very smart as most clients are thoroughly overwhelmed by the volume of data.

I’ve attempted to use named ranges for each segment of the individual tabs but that has proven to be cumbersome due to the number of data tables. Each tab may have 15-20 different data ranges - I have created the individual tabs like this because I’ll run across a Luddite from time to time that just wants to print out all of the individual tabs and look at the data. So I might have Section 1, 2, 3, 4… 17… 18… 19…, etc. on the various tabs. Because of this, I’ll often have headers and other descriptive data that is discarded when I move the data lines to a pivot table. Said another way, I can’t really use a single named range on each individual tab. I’d have to use 15-20.

I’ve also played with Power Pivot and it just takes a long time based upon the size of the worksheets (I’m also using a Mac).

I don’t really have a problem copying the data and making a pivot table from a new sheet. If I later add more scenarios, I’ll just add the new data to the bottom of the consolidated sheet and regenerate the pivot table. The issue is “copying data”… sometimes a client will make a change to one of the individual tabs and not understand why the pivot tables don’t update (because there are two sets of data…).

Imagine you’ve got 35-40 tabs and you want to make a pivot table from the following:

Tab 1: Rows 8-33, 56-70, 101-300 Tab 2: Rows 12-16, 44, 90-99, 456-848 Etc., etc., etc.

You never know if a row or section will be included in the pivot table.

So, if you’ve made it this far, thanks for reading. Any pointers or thoughts about how to create a pivot table off of large disparate data sets on different tabs, in various locations on a tab, which can often be in different places deal to deal, would be greatly appreciated.

Dan :)

Hello.

I have a large excel file for my watched shows list. I put some general stuff about each show in a seperate sheet, and in another I have the date and amount of progress in each series.

https://preview.redd.it/2vdo2s2icw6e1.png?width=2572&format=png&auto=webp&s=4bbe2c9eef8b13bd4494f58ec0ddf8346184d53b

This allows me to automatically figure out the date started, date ended, average time per episode, all of this and more just by adding the date and progress number as shown in the screen shot.

Problem is, while my main sheet with the general stuff can be put into a table and nicely sorted etc., this one cannot, as it is 2 rows wide. I wish to make it so that I can filter and sort this sheet, despite it being 2 rows wide.

I hope I got my point across, Ill clarify anything else in the comments.
Thank you!

Do I have to purchase the 35 dollar online proctor service (https://store.certiport.com/p/12007901) to take the MOS exams online? I have currently only bought the voucher for 100 dollars.

I schedule 2 groups of staff to work in 4 areas. The blue colour code is the CONS group. The yellow is the SPR group. SPR is split into an AM session and a PM session but usualyl stays the same. Each row is one day, Saturday and Sunday are greyed out.

how would I set up a pivot so that I can see how many times for example JM has been with JPG or how many times CA has had TN with them?

I also want to work out total sessions (AM and PM) each SPR spent over the full worksheet (which spans a year).

Is this possible?

https://drive.google.com/file/d/1KNtgqo1lsfNOaFz-pblxHEg2hhAGTn32/view?usp=sharing

So i have rows as unique client codes, column as units they have procured for an sku date wise of one month (1st to 31st) .

in another sheet, i am trying to vlookup the number of units procured against unique client codes. I want to vlookup to be done in such a way, for example i put input value as 1, it should show me output of units procured on 1st (n), 2nd (n-1), and 3rd of the month (n-2).

Again, if i change the input value to lets say 16, it should give me output of units procured on 16th (n), 17th (n-1) and 18th (n-2).

Please help how do i solve this problem? if apart from vlookup, is there any other way we can get such output.

The raw data will be captured date wise of a month in one sheet.

Link to excel file

https://docs.google.com/spreadsheets/d/1ulTMZTeben7L1jCjbbvfH8YKR_ck3Vfj/edit?usp=sharing&ouid=105912753773769265927&rtpof=true&sd=true

Hi all,

I have a couple of sheets that I'm trying to consolidate into one by doing a vlookup using the skus as a key.

The issue that I'm having is that in some of my sheets that contain the SKU it cuts off the 0s at the start.

I'm aware this is because it's a number etc but I've tried to format it to text and it's not working. The other issue is that while they're all skus they can vary. For example, one SKU might be 123, another might be 000123, and another might be 00000123123123 or even abcdefg. Point being is that the length and etc can vary (also working with thousands of different skus).

How do I make it so that regardless of length, some skus starting with one 0, multiple zeros, or no zeros at all in one column etc that I can still vlookup via SKU?

Thank you!

I’m wanting to do the following: “ =AVERAGEIF(Q9:Q43 and Q106:Q147, 1, R9:R43 and R106:R147) “ but I can’t figure out how to make it work.

           Date1.   Date2.   N/C     Date3.   Date4.   N/C

Name1 A. A. NC A. B. C Name2 C. A. C A. A. NC Name3 A. A. NC A. A. NC

I want to filter the data above to only show the changed “C” data in column 4 or column 7. So in this example only Name1 and Name2 would show up. I’m hoping for the filtered data to look like the below:

            Date1.        Date2.        Date3.       Date4.  

Name1 A. A. A. B.
Name2 C. A. A. A.

Is it possible to create this using a pivot table, with a slicer? Or if there’s an easier way? Thank you!

Hi all! I am not a noob in Excel but i need to improve my skills in VBA scrpting.

I have a project where i have to move, cut not copy, a value, for ex: a name between 2 tables, back and forth, using Buttons with macros.

I use 2 tables and i want to move the value always to the last row of the table.

I have searched the internet but i have never found anything quite like i need.

Any help is appreciated, TiA,

Jose

https://preview.redd.it/l6h2syxufv6e1.jpg?width=418&format=pjpg&auto=webp&s=941202950244fd23efa0e8b283880c42b0116d7b

I am dealing with a big redundancy exercise (UK) and need a formula to calculate statutory redundancy pay in bulk. There is a “one by one” calculator on the Government website but I don’t have time to run all the staff individually through it and I need to have all the figures in one place along with the rest of my data.

My spreadsheet has

• Age at redundancy date in Column L
• Capped weekly pay amount (up to £700) in column N
• Completed years service at redundancy date in Column O

The redundancy pay calculation is based on weekly pay (which is up to £700 per week even if actual weekly pay is higher) and is subject to an overall cap of £21,000.

The entitlement is:

• half a week’s pay for each full year worked when the employee was under 22

• one week’s pay for each full year worked between 22 or older, and under 41

• one and half week’s pay for each full year the employee was 41 or older

Is anyone able to provide a formula? If the formula could also account for the 20 years service / £21,000 statutory caps that would be great.

Thank you!

Hello!

Is there any way to find duplicate patterns within a large number of columns in Excel?

I am working with a large amount of text strings and I am trying to find repeating patterns of cells vertically.

i.e. I have a large number of text strings and I want to find recurring "passages" in the columns where identical cells repeat in identical orders.

I've already highlighted duplicate values, but that's not so terribly useful as I have a real ton of cells : are there any good ways to quickly catalogue duplicates and not just mark a text as having duplicates? I could manually go through all the passages with duplicates to see if they cluster, but I imagine there has to be some way to automate some of this?

I have been struggling to describe what it is exactly I want, as I don't work very often with Excel or this sort of data, so I am open to any suggestions on trying to find these patterns!

EDIT:

Here is an example of what I have: I am visualizing stress patterns in an ancient text, and I am trying to find recurring duplicate patterns vertically, down columns.

Having longer stretches of repeating stress patterns will allow me to go back to the actual text and see if there are significant parallels in passages which correspond to identical patterns in stress.

Each cell is an abstraction of the stress pattern of a half-line of poetry, so I am trying to see duplicate patterns across half-lines vertically in these columns.

EDIT 2:

Each of the strings of characters represents a half-line of Homer's Iliad. I have simplified each half-line into a "contour" representing the accentual "shape".

Hypothetically, if B7, B8, and B9 have duplicates, I am trying to find a way to see if there are any other three neighboring cells in the same column that are duplicate both value and order in their respective column.

So I am trying to find a way to not only find all the duplicates, but to find duplicate sections of columns, i.e. if B7-B8 was equal to E200-203 I would like to have some way to know that.

I am trying to get this information in a way that preserves the relationships to the values in the identifying columns, i.e. A in the image,so I can trace back these patterns to the text and see what is happening in the text when these rhythms line up.

https://preview.redd.it/7eqxmnrf9v6e1.png?width=1316&format=png&auto=webp&s=d4a86fec9815ad7c8daedacd0071fdfd34dcec58

I'm trying to get a cell to return the next item in a column based on a word in a different cell. I've included an image that breaks the requirement down to it's simplest form.

I was going to try some form of lookup or offset but since the reference cell and list are in separate places I couldn't get it to work.

I'm obviously a bit of beginner at formulae and it's been 20 years since I learned how to do this stuff in school

https://i.imgur.com/oSUzBmf.png

in this example J3 should return Igloo. if the word in G3 was " mouse" then J3 should return "nugget".

Thanks in advance for any advice.

Hello there! It's my first post here, so what's up!

I wanted to know, that is there any tool or even some AI bot that I can use to fill out a progress tracker that I'm making for myself based upon a document with the topics in my syllabus from my syllabus document?

I'm really thick and trying to learn excel but Google is bringing me no solutions, but I don't know if it's because what I'm wanting isn't possible.

Any help would be great appreciated

The layout I've got is

Colleague > time > h:mm> h:mm> h:mm =

But as you can imagine it's not giving me the right result.

Saturday, December 07 - Friday, December 13, 2024

###Top 5 Posts

###Unsolved Posts

###Top 5 Comments

I have data I collected from family trips to Japan facebook group.

Now I need to find the average nights spended in each city for all the groups.

The data for each family is seperated by a yellow row (like seen in the image).

how do I average it?

https://preview.redd.it/8t7tvsambu6e1.png?width=1138&format=png&auto=webp&s=14b76f23cdb5284b5bc2c0337ebdf10a5ef85f9f

Hello, fellow Redditors!

I am pretty desperate, so you guys might be able to help me...

There are two sheets in one Excel file. The first is for the whole list of articles and items from the trader. The second is a list of EANs with the amount in the next cell.

I now want to multiply the amount from sheet 2 with the price of the article from sheet 1 and put that into the next cell behind the amount on sheet 2.

So basically:

1. get String from cell 1 in THIS line
2. search for String in sheet 1, column 1
3. get value from cell 6 of THAT line
4. multiply this value with cell 2 (of THIS line)

I hope this text is somewhat understandable.

Thanks in advance!

Hello, I have been trying to build a work book that feeds me data from my teams about mechanical parts that have been inspected and tested successfully for an annual service. I recently learned about vstack and using it to bring all my different tables of information into a single stacked column. I use conditional formatting in the other workbooks and tables to highlight part names based on their status but the vstack formula does not seem to carry that over. Is there a way to do that? I’ve even tried writing a new conditional formatting rule for the stacked table, but it does not seem to work.

Thanks for your help.

Hello,

I am having an issue with xlookup

My excel version is: Microsoft® Excel® for Microsoft 365 MSO (Version 2306 Build 16.0.16529.20164) 64-bit

When a value in the source data changes, xlookup will not reflect the change or recalculate until I click in the formula cell and hit return.

And yes, my Calculations Options is set to Automatic so that is not the issue. Hitting refresh or F9 or Calculate Now do not work.

The only way is to give focus to the formula cell and hit return which is not feasible for hundreds of rows.

Does anyone had this issue? What was the resolution / work around?

Thanks in advance