Data Masking Email Using Formulae
Hi guys, I am trying to work on my data masking skills and have ran into a problem. I am trying to mask the email addresses of the following data set (this is a fake set of data from an online course btw). I want to mask the emails such that: email address removed for privacy reasons will then become b******email address removed for privacy reasons . I want to make sure that the number of Asterisks represents the number of characters displaced, I have spent a while trying different formulae, however I have been able to generate a formula for the first email from E2 into F2, although this doesn't seem to work for the other rows of emails. Any help is much appreciated :)
Vlookup returning random #NAs, Randcom Correct Responses
I have a multi-sheet workbook, with a named array of data. On a separate sheet I cite a first-column text of that named array, to extract datum from a column on that specific row. In other words, "VLookup." Maddeningly, there are some instances where the use of one of the text-strings in the array, produces an "NA" error. I can't figure this out, because I have: --Checked to make sure the named array covers the fields to which the Vlookup refers. --Checked (using "EXACT," "Cell=Cell," even simply copying the value from the named array into the formula) to make sure there are no common text/formating/other discrepancies between the citation and the cited cell. --Checked (redundantly) to make sure the cited cell DOES exist in the array. --Tried with other citations (some of which always work, some of which always do not. I will post/attach the file. Please note, the workbook is being developed, and so it's somewhat disorganized, but the two pertinent worksheets are "1 GenReservation Data" & "2 AvailabilityCalendar" The cell in question is in the "2 Availability Calendar", C17. This cell uses the results of B17 as the "Lookup Value" in the array titled "AE01_ReservationInfo." While there might be some thing caused by the formula in B17, it doesn't seem so: As you can see by some attempts by me to figure out the error, I've created a simple stripped-down Vlookup formula to resolve the problem, and the same thing happens, even if I simply copy from the Array the text that Vlookup must look up. I've also used other citations from the "1 GenReservation Data" page both directly and in the formula and they often work. For example, using, (either directly or derived via formula) the text "Farquar 2026-0415" or "Johnsonite 2025-0115" never work. But all the others do. Thanks for your ideas on this one! Sorry, this is my first posting, and I cannot see how to attach the file. Assumed that would be obvious (and maybe it is, but not to me :( ) So if you can help with that issue, I'll be able to provide a better context. But I'll try to include a table to illustrate the issue, even though that will only show . ...and that didn't work either. Claimed my table had invalid HTML and told me to remove tags...which I don't know how to do. Thanks for your help, either way.
Conditional Formatting possibly
Hi guys I'm setting up my work schedule in excel so I can have an over view of my shifts I have my set up complete i.e.. A1&B1 Merge = Day, A2&B2 = Date, A3= start, B3=End, this is repeated across the rows for 7 days this only has information typed in on my scheduled shifts i also have additional rows below for extra shifts and overtime (these work perfect) however on my scheduled days on I'm trying to get the cells for the example A2/B2, A3, B3 and A4/B4 to highlight only if information is available in A3 same for across the row for each day any help would be much appreciated Monday Tuesday Wednesday Thursday Friday Saturday Sunday 29/12/2025 30/12/2025 31/12/2025 01/01/2026 02/01/2026 03/01/2026 04/01/2026 10:00 22:00 10:00 22:00 10:00 22:00 00:00 12:00 12:00 12:00 00:00 00:00 09:30 23:57 00:00 14:27 00:00 00:00 00:00 00:00 00:00Return only one instance of value for a repeated Item Codes in Excel
Hello, I have a large data set at the transaction level. For context, let's start with Item Code#1234. In the month of January 2025, there may be a 1,000 recorded transactions for this 1 Item Code. However, I would like to bring in just one instance of Advertising $$for Jan 2025 for this Item. For example, assume that Item Code# 1234 has an allocation of $11 in Advertising spend for Jan 2025. How do I now bring in just one instance of this $$ for Item Code#1234 when there might be 1,000 recorded sales transactions in the month of January for Item Code#1234? Hope you can help.Need Formula to Pull Specific Data
I am attempting to pull data from one sheet into another if certain key words are met. The data is static but I just need it to read data entered in one column and pull the related CTN. I've attached a spreadsheet for reference. For example, on the "Main tab", I enter AKL, SYD, BAH into the WCO column and I want the CTN column to read the WCO column and pull the correct CTN from the "CTN tab" by reading the table of information.
Formatting Rows and Columns in Large Spreadsheet
I have a spreadsheet where Pledge IDs are displayed in Column A (thousands of rows), the the associated Pledge Payment IDs are on the same row, but extending for hundreds of columns. Here is an example of the existing format, and how I need it to be formatted. All suggestions would be most welcome. Existing Format Column A Column B Column C Pledge ID Payment 1 ID Payment 2 ID 12345 4501 4502 Required Format Column A Column B Pledge ID Payment ID 12345 4501 12345 4502
I need some help digitising our paper order system, but struggling with the code!
I have 4 sheets: 1 where we fill out the orders our students place, 1 where we calculate how much we owe the retailer,1 with the price list in case it changes and 1 for students with discount. Would anyone be able to help with the following? Link the items to the price list and the amount ordered. It needs to show correctly in the PRICE column for the item if a student orders 1, 2 or 3 items. If DISCOUNT = yes, in the PRICE WITH DISCOUNT column, I need the price to be calculated with an 80% discount In the 'order to retailer' tab, I want the excel to see how much of each item is ordered and make a sum of the price WITHOUT discount I want the data (student name, total order amount, amount with discount) from the order sheet to automatically go into the discount sheet if I selected 'yes' to discount and also for the amount that was discounted to be be displayed in the 'to be paid by the city' (students with discount only pay 1/5th and the city pays 4/5th) Please would someone be able to assist? Thanks a million!!!Payback Period
In my file, how should I adjust my payback period formula so it "always correct", in that it always uses the correct formula as in the year it is not negative for the cumulative cash flow row? Is there anything else incorrect in my other financial ratios? I need a sanity check on NPV, IRR, ROI as well. The current formula for Payback is: INT(C3+ABS(C36/D34))&" Years and "&ROUND(C3+ABS(C36/D34)*12;0)&" Months" NPV: SUMPRODUCT(C34:G34;C39:G39)+B34 IRR: IFERROR(IRR(B34:G34);"N/A") ROI: IFERROR((SUM(B23:G23)+SUM(B32:G32))/-SUM(B32:G32);"N/A") Many thanks in advance!
Pivot Table Yearly Sales Report Issue
I'm currently working on creating a pivot table for a yearly sales report. However, I'm encountering an issue where, after a particular row, the year is not being recognized. This results in an error. Can someone please explain why this might be happening and guide me on how to resolve it? Additionally, is there a way to directly convert the date to year within the pivot table without having to separately convert the date to year first and then use the pivot table?
