Forum Widgets
Latest Discussions
Find out & highlight duplicate data(number)within same excel sheet in diff column or in diff sheet
I have number in excel column A like "5434567880765" and so on & in column B a JSON Data is present where the no "5434567880765" may be present? So how to found the duplicate & highlight it in the same or different row in that excel sheet ? PFB the Excel column A & Column B example, It's may be present in same sheet or different sheet. A B 4356778 "team":"In","test":"4356778","IsPresent":"True" 9876546 "team":"US","test":"9876546","IsPresent":"True" 5435657 "team":"US","test":"2874541","IsPresent":"True"thisisanirbannMay 24, 2025Occasional Reader64Views0likes2CommentsExcel Add Text after first character _
Hi I was asking people to help me with adding text and although I hoped they may have answer none of 30 excel day to day excel people could help me and I am hoping that there is bigger expert here so they may give me some hints I have test like this DVS.101_SERVICE-LAN-IN_172.16.25.1 DVS.2047_INTERMEDIA-WEB_test DVS.2070_cCFS_DMZExt_192.168 Now I only need to add work SYN after first red character. I have used Kutools and although good it adds this word after every _ character which is not what I want I would need to add only word SYN after first red character. It doesnt work by position because some have 8 or 9 position so that wouldnt be the patern NOt sure if excel can look and only add that word after first _ character and not the rest of the cellcer113May 24, 2025Copper Contributor45Views0likes5CommentsDynamic Calendar in Excel 365
Hi, I create a dynamic array formula for projects at work. It takes project start & end dates and returns a calendar where its duration is based off of those two inputs. Please let me know if there's any errors I missed or it can be improved in any ways. 3 inputs to define the calendar size: start_date & end_date (vertical) and mth_in_row_num (horizontal) Format Cells: 'd;d;;@' to show date and text and hide 0 Conditional Formatting: draw calendar borders grey out Sat and Sun dates highlight holidays in red highlight milestone dates in corresponding colors CA/US Holidays: drop-down list to switch holiday region Holidays tab: refer to the attached excel file Edit: The calendar formula and conditional formatting have been updated as djclements pointed out the rows of calendar should be 6, not 5, to capture the missing 30th or 31st in some months. =LET( start_date,$B$7, end_date,$B$15, mth_in_row_num,3, mth_num,(YEAR(end_date)-YEAR(start_date))*12+(MONTH(end_date)-MONTH(start_date))+1, mth_num_mult,CEILING.MATH(mth_num,mth_in_row_num), mth_num_div,mth_num_mult/mth_in_row_num, cal_col_num,7*mth_in_row_num, cal_horiz,DROP( REDUCE(0,SEQUENCE(mth_num_mult,,0), LAMBDA(a,v,HSTACK(a, LET( mth_start,EOMONTH(start_date,v-1)+1, cal_head,HSTACK(INDEX("",SEQUENCE(,3)^0),TEXT(mth_start,"mmm-yyyy"),INDEX("",SEQUENCE(,3)^0)), cal_week,TEXT(SEQUENCE(,7),"ddd"), cal_body,SEQUENCE(6,7,mth_start-WEEKDAY(mth_start)+1), cal_body_filt,(MONTH(cal_body)=MONTH(mth_start))*cal_body, VSTACK(cal_head,cal_week,cal_body_filt))))), ,1), DROP( REDUCE(0,SEQUENCE(mth_num_div,,0), LAMBDA(a,v,VSTACK(a, CHOOSECOLS(cal_horiz,SEQUENCE(cal_col_num,,1+cal_col_num*v))))), 1) )DaeyunPabloMay 24, 2025Brass Contributor2KViews1like5CommentsMove up to next blank row after copy/paste from previous sheet.
Snowman got me rolling with code that does almost exactly what Im trying to do. After I hit end of day button Im trying to get the copied data to move up to the next blank cell in column B within a range. If I have any data in B2:B9,B11:B21,B23:B29 marked with a t next to it in column A and hit the end of day button only data in cells marked with that t are moved to the next sheet in the same cells they came from. What Ive noticed is only data in B2:B9 go to the exact same cell. B11:B21 are offset by 1, and B23:B29 are offset by 2. This is fine as the data is still moving. What I want it to do is for those ranges move up to the next blank cell in column B. So if I have data only in B4, and B6 I want that to transfer to next sheet in B2,B3. The same for the other 2 ranges. I also dont want it to overwrite anything that may already be in a cell on the next sheet. If I have "Tree" in B2 of the next sheet I want the data to go to B3,B4. Same for the other two ranges. I have tried xlUp, xlDn, and played with the code that was given to me by Snowman to try and make it work. Im not getting anywhere with this. I thought maybe if I create another macro that after I transfer the data would move everything up into blank cells then Id be okay with that also. Im not having any luck with that either. I even recorded a macro for copy/paste but that wont work if there is data in a cell already that I need to keep, and not be overwritten. I have attached a copy of the workbook. Hopefully this time it will allow it to be published with this query.BrianP475May 23, 2025Copper Contributor50Views0likes4CommentsSum from cell across multiple sheet, depending on hire date
I can not figure this out, I am trying to make a summary sheet that will count the vacation days across multiple sheets from "Start" to "All" (these are timesheets between those named sheets) for employees. But I need the count to reset on their hire anniversary month and day. Employee 1 - Hired on 1/2/2001 (found in cell B5 on the sheet named "Summary") Employee 2 - Hired on 4/9/2020 (found in cell B6 on sheet named "Summary") In this example, any vacation days in cell F5 (employee 1) across multiple sheets between sheets named "Start" to "All" will count until the anniversary month and day arrives, then it will reset and start counting forward again until that date arrives again. Any help will be much appreciated!SolvedLisaB1009May 23, 2025Copper Contributor302Views0likes15CommentsHow can I adjust this VBA code to effect the sheet not the workbook
I have this code for clearing checkboxes from an inserted object, but it clears the entire workbook. Does anyone know how to adjust it to clear the sheet it is placed in. Please note I chose this code because the checkboxes are in groups and it was still able to clear the boxes. Thanks Sub Oval1719_Click() Dim sheet As Worksheet For Each sheet In Sheets On Error Resume Next sheet.CheckBoxes.Value = False On Error GoTo 0 Next sheet End SubitsMontyMay 23, 2025Brass Contributor39Views0likes3CommentsCharts not displaying
Office 365 recently updated. None of the charts on separate worksheets from earlier recent Excel spreadsheets will display. They will display on the same worksheet as the data, but cannot be moved to a separate chart worksheet. Tried everything to fix this. Help.drdata1979May 23, 2025Copper Contributor36Views0likes2Commentsformule Excel : combien de fois personne X est de "garde" un samedi ou dimanche
Bonjour à la communauté je cherche une formule dans tableau Excel pour dénombrer le nombre de fois où un médecin est de "garde" le samedi et le dimanche selon schéma suivant : j'essaye avec "Nb.Si.Ens" mais cela ne fonctionne pas merci de votre aideKemet33500May 23, 2025Copper Contributor26Views0likes1CommentHow do I return multiple continents for multiple countries?
I have a list of soft drinks being sold in different countries. I want to return the continents for them. i.e. Soft drink | Countries | Continents Coca cola | China, Denmark, Italy, Australia | Asia, Europe, Australia Fanta | Korea, Japan, China | Asia I have the corresponding countries to continents ready. But it seems like no formula works for thisguitarher44May 23, 2025Occasional Reader36Views0likes1Comment
Resources
Tags
- excel42,730 Topics
- Formulas and Functions24,787 Topics
- Macros and VBA6,424 Topics
- office 3656,057 Topics
- Excel on Mac2,659 Topics
- BI & Data Analysis2,382 Topics
- Excel for web1,930 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,648 Topics