I have three sheets: Sheet 1 will display and sort the names from Sheets 2 and 3, while Sheets 2 and 3 are where the list is located and where you input names. The last name you input, whether it's from Sheet 2 or Sheet 3, will appear last on the list in Sheet 1.

Hi, I'm very new to excel and I'm trying to find a way to substitute each code in letters in my dataset with the corresponding code in numbers. My dataset is on the left, the table on the right has the codes in letters and numbers for each country.

A screenshot: https://ibb.co/wQ0CT0H

Is there a way to do this automatically?

Thank you!!! :) and sorry for my bad english

I'm looking for help in parsing out voter data from the county that would be in the "Raw Data" column in my example pic. They give me a cumbersome PDF to work with.

1. Extract out the number in the beginning of the string, which may be 3 or 4 digits.

2. Extract out the name that is listed between the house number and EITHER "New Voter" or [Date]. Name may have multiple middle or last names (ie, Row 6)

3. If New Voter is listed, put that in the Status Column. Otherwise leave blank

4. Extract out the Registration Date.

The below shows an example of what i would like to happen. All Data is obfuscated with fake names. Ideally, the street # would be merged cell for the # of rows that the street number is the same.

I could likely get street # easily with LEFT and TRIM, but i couldn't figure out the rest.

https://preview.redd.it/au9nrsxq160e1.png?width=1798&format=png&auto=webp&s=5907c5355fa797a9cef4fd9732221171c13eebb1

I have 2 tables with columns headed "individual", "age", "increment". "Individual" refers to the ID of a specimen, and "increment" is growth at a year of age.

(I would include an image but idk how to do it without the post getting removed)

The tables have measurements by different people for the same individuals, but ages were estimated differently so the columns are uneven. I want to average the increment values for each year of age for each individual between the two tables. Is there a way to synchronise the tables by "individual" values (so the 1s of one table are in the same rows as the 1s of the other table, 2s with 2s, etc ) even if it leaves blank cells owing to the uneven frequency? Or is there an entirely simpler way to achieve this?

Using Excel 365. I have a pivot table (collapsed) with top level being "State" and next level being "Person". There is a slicer for each of these. If a person wants to see their own data they can select their name in the slicer, but the pivot table won't expand, so all they see is the State name, although the values are applicable to only them as the Person. Is there a setting that will make the pivot table expand so the person sees their name there when they select it from the slicer?

Hi, i would like to know if there's a function that can return a date and its relevant title. For example, i want to find a date and the title for ones with a value of 274.

So in this example above, i would have four dates with its relevant title - ie. Sales (1)

*The image is in the comment section sorry.

Hi there

Could anyone tell me the formula I need to work out unique companies that have shares totalling >0? I'm trying to use the unique= function but not sure what to do next (i.e. how to filter companies that add up to more than 0 shares).

https://preview.redd.it/qr6dputlf50e1.png?width=174&format=png&auto=webp&s=b4410c00e8eae8fc0c70ca418365daceec8ebccf

Thanks

https://preview.redd.it/xelin3dcc50e1.png?width=904&format=png&auto=webp&s=98a85a140f7f3d4b293e837e4bfaff7876785e19

So what im doing is that i have two collumns with numbers, and i take the average of the numbers on each collumn, but only of the numbers that are both filled on a row.
so in this example picture its like this:

. 1 -2
. 5 5
AVERAGE=3 1,5

The way i currently do it is like this:
=IF(ISNUMBER($B22); AVERAGE( IF( NOT( ISBLANK(B2:B16)) * ISNUMBER( E2:E16); B2:B16)); "")
but i feel like theres maybe an easier way to do this? do you guys have any suggestions?

I’m trying to do an assignment in Excel, and trying to multiply two cells. One with 295000, the other with 3.02. My excel keeps coming out with 890271, while a regular calculator comes out with 890900. What is causing this?

I m trying to get an excel file to automatically close at specific times during the day, specific at noon and 16pm. Does anyone know how I can get this done? Chatgpt's answers do nothing so I have no idea what to do. Can anyone help?

Hi everyone,

I’m currently working on an Excel sheet to help my company manage incoming correspondence, specifically to assign each document its corresponding personnel file number. I’ve created a sheet to handle this process, but it’s currently quite messy and could use some refinement. I’d appreciate any tips on improving its organization and efficiency.

Here’s an overview of what I’ve set up and where I could use some help:

• File Number Assignment: When I determine the recipient of each document, I assign a number in pencil on the document itself, then log this number digitally in Excel.
• File Request Form Automation: I have a list setup on one tab that pulls from a master listing to automatically populate a file request form, a process that was previously handled manually.
• Updating List Entries: I frequently double-click the columns to refresh the listing in the request section.
• Checkbox Usage: My co-workers often ignore the checkboxes, opting instead to mark an “X” or checkmark in the bottom corner of the cell with the file number. I’m considering:

o   Removing the “Found/Not Found” options

o   Using a single checkbox to indicate “Found” (leaving it unchecked for “Not Found”)

o   Adding alternating colours for even and odd rows to visually track which row aligns with each checkbox.

• File Number Multipliers: To streamline entry, I’ve added multipliers so that, for example, instead of entering “12876” as a file number, I just input “876” to denote the 12-thousand series.
• Conditional Formatting for Division Heads: I use conditional formatting against a list of file numbers associated with division heads so I can visually identify these while entering data.

Any suggestions on organizing or simplifying these steps would be really helpful! Thanks in advance!

here is a link I uploaded to google drive what I made so far

I attached a copy with any personal data removed to demonstrate what i have created, maybe you can suggest what I can change or improve even if it is just videos on what you suggest will be helpful and a HUGE thank you for you time. I am new there and trying to make an impression so far I just used YouTube. All tips appreciated and welcomed!

Excel version 2019 .

I need a bit of help with this, I have product in row A2, all the product info in multiple columns - including images links. What I need to do is take the image columns and place them below the product SKU (shopify import for multiple images) is there a formula to do this? Attached is the export from shopify, and also the source data file. Export shows image files below the SKU. I really hope this makes sense.

https://preview.redd.it/v6ecbbsu360e1.jpg?width=1229&format=pjpg&auto=webp&s=4e208140ed864bdd466f8beac4c71a9997ad4c7d

This is a very niche problem I'm having and I'm not too good at excel, but I like to test my football knowledge a lot with things such as the Immaculate Grid. I had an idea of creating an excel sheet to name every player I can possibly think of sorted by the teams they have played on.

For example, the main sheet in this would have a space where I enter the name of the player and what teams they have played on (For example: Peyton Manning - IND / DEN). Then I would hit enter and it would pop over to the Broncos sheet, in the broncos/colts section of that sheet, along with the colts sheet in the colts/broncos section of that sheet. I want to be able to list more than 1 for a given section and more than just 1-2 teams as well.

For example, I want to be able to input Philip Lindsay after Peyton Manning is already inputted. Philip Lindsay played for the colts and broncos as well, but also played for the Texans. Then when he is inputted he goes to the same sheets as Peyton Manning, without removing his name, along with going to the Texans section of those pages and the Texans page as well.

I know there would be a plethora of other ways to improve this as well such as inputting the position they played as just a way to see the positions that have moved across teams that I know, but if I could just figure out how to make the main page have an auto sorting capability where the players can go to the sheets that I input, the rest would be tedious sorting work.

I know this is very exact, but this is the vision I have. Does anyone have any ideas on how to accomplish this on excel or is there a different program where this would work better?

Hey guys,

I created a dropdownlist linked to a few pictures in another sheet. My problem is, that every picture is getting displayed as "Bild" (German for picture) inside the dropdownlist. How can I display different names (e.g. Wheelchair for my Wheelchair Picture).

Hey! Im working on a worksheet that have around 2M cells with FILTER function, INDEX, and more inside each cell (23,000 rows * 88 columns)

The worksheet is very, very slow. I need to work on the data to perform further analysis. What is the best way to copy-paste as values (so it wont be formulas & thus making the excel work faster)?

I really need your help!!!

Thanks in advance

To get the target to date, I have to divide the target by the number of workdays in the month and multiply by how many workdays it has been so far.

For example:

Target for the month of November is 210.

Current date: 8 Nov 2024

Target TD: 210/21 x 6 = 60

The target TD is calculated and shown on the monitor every day, so that salespeople know what they need to achieve.

The problem is, the team wants to modify it so that when the salesperson is on leave, it has to stop computing for the day and show the last workday's target TD instead.

So if there are 21 days in the month and salesperson A is on leave for 5 days, the target should be divided by 16 days only. And if he/she is on leave on the 11th to 15th, Target TD should not increase during this time.

I don't know how to update the formula. We have a holiday calendar, so I know when the team is on leave. I can put that in a separate column/s and that's what the new formula should reference, but I don't know how exactly to calculate it.

https://preview.redd.it/qaw247r5540e1.png?width=1187&format=png&auto=webp&s=15da1fd4d5a392073b61c569de07f863a71c2a98

Please help!

Hi,

I have a spreadsheet that summarizes data extracted from a database. Each week about 10k rows of data are exported and entered into the sheet. One of the formulas pulls the date out of the date/time and gives the week-ending date. The issue is that sometimes the date/time in the database has an asterisk at the end and I have no way of removing it in the data. I'm trying to accomplish pulling the date out of the bad data and calculating the week-ending date without using text to columns. Any ideas here would be appreciated. Here's an example:

https://preview.redd.it/kk9if5ky440e1.png?width=395&format=png&auto=webp&s=a3b0e14fdbfbab0795f1b7011d0435d8a2199a6e

Thank you!

Hello Everyone,

I am trying to reprocess a database that goes as follow. The database is an ageing balance (if some of you are accountants you can relate). The idea is that you have a list of invoices by maturity, in this example I have a invoice of 400 that is +120 days old. I also have credit note which comes in negative (in cell B3 here). The idea is to reprocess this table in order for the credit note to impact the oldest invoice. In this case, the 600 credit note impacts the 400 in cell E3, the remaining 200 impact the cell D3 that's why I have 1000 in K3. The rest is not impacted because the amount was fully used on the 2 oldest type of invoices.

I would like to know which formula I can use on cells H3 to L3 to get to the expected result.

Thanks a lot for your help and sorry for my english, it's not my mother tongue :)

https://preview.redd.it/s00ni2snz30e1.png?width=1234&format=png&auto=webp&s=ff7198b0048575f5517d4111780fb3525f1c7cd5

importing contacts to outlook from mac contacts via csv got the contacts messed up. apart from the name all relevant info ended up in the "notes" field of outlook. I did an xls export from outlook to clean up data and re-import.

the problem is that the number of cells per contact isn't constant, otherwise I could distribute the data from column 1 to the correct cells with =WENN(ISTZAHL( SUCHEN("address 1";A5)); B5; "") per contact.

a workaround for that would be to make number of cells per contact constant by adding empty rows. but with +10k rows this will be prone to errors.

i've also tried finding a solution with power query but I lack the knowledge to solve the problem and the solutions I can find don't match with what pq in mac is capable of doing.

is there a formula that can check which data is available per contact and then copy that to the relevant cells?

here's the example file:
contacts no data.xlsx

https://preview.redd.it/ihwrrqx7w30e1.jpg?width=6200&format=pjpg&auto=webp&s=b39844217093b4a769b6397deb22556ba87249f7

Hi everyone, I've been struggled for awhile because the condition is a range and return value also a range.

i want to return the value that in column G:H of tab Evaluations main data (each cell have different value of date), the conditions are: in tab Evaluations main data column project ID (col C) matches with Project ID (Col M) in tab Week Lookup, and col CA:CG in tab Evaluations main data matches cell C5 in Week look up.

Because the return is a range, where I want 1 value in that range that match the conditions above to return, so it is harder. Very appreciated if anyone can help me

The circle in red is the column return value I need, when the week in colunn CA:CG matches the week in cell C5 of week look up tab, and the project ID matches the cell M5 in week lookup tab

https://preview.redd.it/q47yet99240e1.png?width=2642&format=png&auto=webp&s=775d68e22eb9790e092f676d6ecb7f4d22e9ebb5

As a Controller, I use Excel a LOT to organize data. And EVERY time I try to make notes on that, Excel automatically assumes that I want the cell to be as tall as it needs to be to accomodate those notes.

I know how to turn off wrapping in a specific sheet, but having to do it every time is a hassle. Especially because Excel somehow keeps turning the wrap function back on if I add rows or columns.

How can I tell Excel to NEVER wrap cells automatically, without having to fiddle with the settings every time I make a new file?

Hello all,

I am a novice/intermediate Excel user with Office365. In real life, I'm modeling IRA withdrawals and I'm running into a problem that can be distilled into example included here.

I add or subtract annual interest from the balance using FV and I make a withdrawal based on the amount available. When the interest rate is negative, I cannot figure out how to determine the maximum withdrawal. If I withdraw the start of year balance, the result is a negative number (which is not OK for the rest of the calculations). If I use the end of year balance (or start of next year), I run into a circular reference.

I feel like I may be making a fundamental mistake, hoping for some direction.

Withdrawal of Start-of-Year balance results in negative balance

i dont know if its the fact that the letters are the values, but ill get the basis for the chart without any bars

https://preview.redd.it/g1fg188mt30e1.png?width=2928&format=png&auto=webp&s=70e950e1db3a45ddc02887f98e4110cdcdf26eb1

I use online versions of both Excel and Google Sheets.
I want to transfer a little 10x10 table worth of DATA from Excel to Sheets but none of the online services having an API does the DATA converting. They are all faithful to the spirit of the Excel file and when "converting" they just output the formulas not the values. Same for Zapier, SheetGo etc...

Of course the Excel formulas are incompatible with Sheets so it does me no good :(

Couldn't get NodeJS or SheetJS to to do it and I even tried to use PowerShell to importXLSX and exportCSV but even then some values are not exported, just the formulas. When looking inside the XLSX file, there is a Sheet1.xml file but even there some values are missing.

Sheets has a URL based way of downloading a spreadsheet in CSV but Excel doesn't apparently.

So is there any way besides saving the Excel file manually as CSV to get the data out of the file ?

Last resort would be to open the file with Edge and run an AutoHotKey macro to save the file as CSV but I would really like to avoid doing something so "unaesthetic" :(

I created a table today with checkboxes in the cells (via the insert tab not the developer tab) and they worked exactly as I had hoped.

However, when opening the same document on my girlfriend’s laptop the boxes are just presented as TRUE or FALSE.

She also cannot add checkboxes via in the insert tab even with the developer setting enabled.

Anyone had this issue?

*newbie alert* Hello there! I am putting together a summary of my home network in a spreadsheet. Composed of the following sheets:

Sheet no.1 (named 'Primary') has a summary of all the networks based on the vlan id. For example on row 5 there will be info regarding vlan 5 (columns are: vlan id, description, network, dhcp server (if present), released IP addresses), row 6 will have info regarding vlan 6 and so on.

Sheet no.2 and forward will have detailed information about the network. Each sheet is named 'vlanX' where X is the VLAN ID. I'd like to pull information off the Primary sheet based on the value of the VLAN ID that I will put in a cell (B1 in my case). For example if I am on sheet 'vlan5', on cell B2 I need the value of the primary sheet, column C, row 5.

So my question is: How can i retrieve the value of a cell in another sheet based on the value of a cell in my current sheet?

I hope I explained myself clearly! Cheers!

Hello, for my statistics course, I was provided with a set of data. (As shown below; goes down to 2445, so, lots of data). There are four types of water that I have found in fourth column: PSW, AtC, ArW, TAW. Now, for each of these waters, I need to calculate the mean and standard deviation of depth, temperature and salinity. I have no clue how to use the formulas for these when I have all these waters together in one column. Should I use the "if" formula somehow? I can't think of how to do that correctly. Please grant me with some tips! Much appreciated!

Edit: It is the newest version of Excel as I just recently downloaded it

https://preview.redd.it/xxofmwmhc30e1.png?width=422&format=png&auto=webp&s=b1fb78cebd5ef7b5b8c71aa531a2e45ffb7097fd

Hello,

I have a set of numbers, I need to put in excell and don't know how to do it.

The numbers I have got are in text form like this: 230,33 245,45 244,00 260,34 etc. (example)

I need to put the numberst into separate columns so that each number (230,33) has its own column.

how do I do that?

thanks!

Hi, I'm struggling to figure out how to get the mean value for each day of the week. The link I've provided is part of a much larger dataset, there's quite a few outliers with much higher/lower values so I'm keen to have the median instead of average if possible. Thanks!

https://imgur.com/a/pGlFgS2

https://preview.redd.it/ztxdscdzy20e1.png?width=228&format=png&auto=webp&s=332ff46b3038efde0fec97bb77586a0787ae9bc9

So I'm tracking my friendship expenses and I can't find a way to sum it up properly. Looking previous posts with this problem, I found the formula

=SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(LOWER(A2:A20),"t",""),"m",""),0))=SUM(IFERROR(--SUBSTITUTE(SUBSTITUTE(LOWER(A2:A20),"t",""),"m",""),0)) 

Which was ineffective for some reason. I did use B3:B16 so I'm not sure what the error was. Alterntatively =TRIM(LEFT(B3,SEARCH("(",B3&"(")-1)) was used with a different column to eliminate the text but then the sum values did not work out (came to 0). So I'm not sure what formulas I should be using ngl. I'm not an excel user, I just wanted to track my expenses for the year quickly. Thank you!