Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x

Microsoft Excel

132K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

I need to replace commas "," with newlines in Excel.  There could be 1000 of these (in specific columns).

I can't find the mechanism for doing this via the Find & Replace.  I've seen things like Ctrl-Shift-j (but that doesn't work) as the newline.
0
Get your problem seen by more experts
LVL 11
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

I am trying to find a formula that returns the earliest date from a table called DC_Table that has 11 non-contiguous 'Date' columns, excluding blank cells or cells = 0.

All cells in the DC_Table are linked cells, and for the purposes of providing an spreadsheet example I have added the source spreadsheet to the workbook. It's called DC_WOs_Extracted.

There are several non-continuous columns in DC_Table that are date fields [Date1], [Date2], etc., and the alternating columns are 'Minutes' fields [Min1], [Min2], etc.

As shown on the worksheet tab called  'Dates', the following formula works but seems cumbersome. It references the first 3 [Date] columns (ultimately I will need to include columns Date4 through to Date11), and returns the earliest date found:

=IF(MIN(DC_Table[[Date1]:[Date1]],DC_Table[[Date2]:[Date2]],DC_Table[[Date3]:[Date3]])<>0,MIN(DC_Table[[Date1]:[Date1]],DC_Table[[Date2]:[Date2]],DC_Table[[Date3]:[Date3]]),"")

I am hoping for a formula that is simpler (ie., using the range as opposed to having to list each date column as above), as some of my spreadsheets will have up to 25 [Date] columns. Is that possible?

Thanks,
Andrea
Metrics_DCWOs_01FEB2018_EE.xlsx
0
Hi,

In the attached spreadsheet i need some match index formula in tab "Pnl" which is looking up tab "Data_Tab"

I need the totals for each weekending (i have manually entered some for guidance)

In row 11 i have put the names of the columns the totals need to match against

Can someone provide the formula so these numbers will fall out in the pnl tab

Thanks!
Seamus
HOWL-REPORT.xlsx
0
Hi, i need the countif formula for

one if a number is >10 or it is greater than -10 eg -11

Thanks
Seamus
0
I have an Excel formula that looks like:

=IFERROR((MAX((((G11-$M$5)*F11)*$M$6),0)),"")

The corresponding Access form fields are:

G11 = txtMST
M5 = txtMoistureMax
F11 = txtCleanCWT
M6 = txtDisSchd1

How do I convert this to Access?
0
What VBA code would I use to determine the new range if a user copied/pasted the range to a new location. (See Examples)

Example1:

Starting Range: C20:F30
User Moves Range to: H4
Solution: In this case, if the user cut C20:F30 and pasted it into cell H4 the new range would be: H4:K14.

Example2:

Starting Range: AB42:AD60
User Moves Range to:DD50
Solution in this case, if the user cut AB42:AD60 and pasted it to cell DD50 the new range would be: DD50:DF68

Example3:

Starting Range: AB42:AD60
User Moves Range to:A7
Solution in this case, if the user cut AB42:AD60 and pasted it to cell A7 the new range would be: A7:C25
1
looking for V_OUT sheet

for example im looking for 467

appear 7 times in each column
65434677665v1--1-___121.xlsm
0
Experts:

I need your help with creating a conditional name range.    Here's what I have thus far.

Cells A1:A10 are saved as name range "Customers".    I then use that name range ("Customer") as a drop-menu in another cell.

Some of the 10 customers (naturally, I have more in reality), however, had not made a purchase yet.   That is, customer in A3 or A6 or A10 have placed 0 orders.   Therefore, I want my drop-down menu to only include the other 7 customers (A1, A2, A4, A5, A7, A8, A9).  Once, any (or all) of the other three customers placed an order, I want the drop-down menu to reflect that change and show up to 10 customers.

My question:   How can I create this dynamic name range (drop-down menu) where I only include customers where value in B1:B10 is greater than 0?

Thank you,
EEH
0
I'm trying to translate this from an Excel worksheet:

=IF(C11<1,"",IF(E11>$M$7,(((1-$M$7)*C11)-(((E11-$M$7)*$M$8)*C11)),((1-E11)*C11)))

to an Access form field calculation.

Note that in the Access form these are the cooresponding field names:

C11 = txtGrossCWT
E11 = txtFM
M7 = Header txtFMMax
M8 = Header txtDisSchd2

I don't even know where to begin.  Any help would sure be appreciated.
0
Below is an example of dates I have in a spreadsheet, the dates are stored as a number. It's MMDDYYYY format with no leading zeros.  How can I convert this to a proper date that excel can recognize?

From Date
2022017
2022017
2022017
2262017
2212017
2152017
2242017
2022017
2172017
2142017
2172017
2192017
2242017
2262017
2022017
2252017
2252017
2282017
2062017
2022017
2102017
2262017
0
Free Tool: Subnet Calculator
LVL 11
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Hi all,

Attached is a spreadsheet where numbers will be added into the tab "Data_Tab", i need those numbers to displayed in tab "overview"

The numbers i need filled in tab overview are highlighted red, they need to be matched via type eg "Z Read" and date "01/01/2018"

Attached is the spreadsheet  I am using

Thanks
Seamus
HOWL-REPORT.xlsx
0
Have this solution

then


need get like this

090909090990909090909090909090909909.PNG5653247890344.xlsm
0
I had this question after viewing how to convert m/d/yyyy to d/m/yyyy.

please see attached example dummy file, i need a macro to to clean up the messed data input.

there are two worksheets in the attached workbook.  Sheet called "messedup" is what i have right now.
the sheet "Cleaned" is the desired solution that after i run the macro it should give me that.

any help is appreciated.
EE2.xlsx
0
i received an excel file where dates were stored as text and it was m/d/yyyy

my computer setting is d/m/yyyy  

i do not know how to convert that m/d/yyyy to d/m/yyyy

8/4/2015
8/13/2015
8/17/2015
8/19/2015
8/21/2015
8/24/2015
8/25/2015
8/26/2015
8/27/2015
10/23/2015
7/23/2015
8/1/2015
6/2/2015
5/19/2015
12/29/2015
9/16/2015
6/9/2015
8/11/2015
10/21/2015
5/6/2015
5/28/2015
10/13/2015
10/13/2015
EE.xlsx
0
hello , i wish to have your help for the attached file.
i have table have many columns with large amount of duplicate values.
column A have many duplicate values but the other columns ( B,C,D, etc ) will have different values.
I need to search I the  table for value mentioned in column named “ code “ based on 3 conditions mentioned in the list


hope its clear in the attached file

thanks in advance
search-for-code.xlsx
0
i have the start and end date as shown in the attached file and i want to create months like shown from column C to N

the yellow cells needs to be formula.

thanks,.
Book1.xlsx
1
A user would like to enter ranges in an Excel workbook. What VBA would I use that would label in column C whether the ranges he/she entered are valid or not.

EXAMPLE:

Row       Column:B            Column:C
  1          A1:B200                  Valid
  2          BC10: DD10           Valid
  3          A:B200                    Invalid
  4          C200:A1                  Invalid

The code should say row 3 is an invalid range because it missing the row reference after the "A".
The code should say Row 4 is an invalid range because column "C" is before column "A".
0
Hello, I would like to make some edits to my code to make it work with different types of worksheets. Currently I'm copying and pasting fixed cells, but I would like to incorporate search/match functions in case the information i need are in different cells on different worksheets.

Worksheet Information:
- B2 to B5: Titles of department info
- C2 to C5: Corresponding department info
- Row 8: Header for products
- Cell A8 = serial number. Cell A9 = "Product Information"
- If the department's worksheet has no products, Cell A9 would say "NIL"
- However, some departments might have different formats (eg extra blank column or extra row which cause the code to copy the values

Code's current function:
1. Add 4 columns on left
2. Copy and paste C2 to C5 (now G2 to G5)
3. Copy rows and paste on another sheet (from row 9 to last row)

     
        'Loop each worksheet
            Dim wscount As Integer
    Dim a As Integer
    wscount = ActiveWorkbook.Worksheets.Count
    For a = 1 To wscount
        'activate worksheet (a)
        Worksheets(a).Activate
        'skip if NIL
        
        'Add column and paste department info
        Dim i As Integer
    
Columns("A:D").Insert shift:=xlToRight, _
      CopyOrigin:=xlFormatFromLeftOrAbove 'or xlFormatFromRightOrBelow

    
    'Loop for cells with content
i = 9
    Do While Cells(i, 6) <> "" 'Currently this includes "NIL"
        Range("G2").Copy
        Cells(i, 1).PasteSpecial Paste:=xlPasteValues
       

Open in new window

0
looking for V_OUT sheet

for example im looking for 65 it appear 8 times

but when hit find 2 it only show 3 times
65434677665.xlsm
0
Keep up with what's happening at Experts Exchange!
LVL 11
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

have this solution need can button to clean the HIST_DTA
29078876--2-23421.xlsm
0
need the percent as show here
needed

222222222222222222222222222222222222.PNG29078870aaaaa.xlsm
0
need create an history to be show  in HIST_DTA






of the selected  i mean to compare ( what  need to be selected )

888888888888888888888888888888888888.PNG29078859b.xlsm
0
1-need the find numbers be show as the result is  show  in A4   V_OUT sheet

2-need show the total columns od data in b1  V_OUT


666666666666666666666666666666666666.PNG8598744458.xlsm
0
I am attempting to create a xls that allows the user to input a Emp Id under the correct Manager category that will lookup the Manager name for another worksheet.  What am I missing.  I have included a sample file.

Data extract sheet the user inputs the ID and return the name, except my version is not returning the correct manager name, except for col D:E.

What am I doing wrong:

=IFERROR(INDEX('EMP LIST'!$A:$A,MATCH(1,'EMP LIST'!H2,0)),'EMP LIST'!$B:$B)

Open in new window

kfsSample.xlsx
0
hello , i wish to have your help for the attached file.
i have table have many columns with large amount of duplicate values.
column A have many duplicate vlaues but the other columns ( B,C,D, etc ) will have different values.
i need to search in the table which contain large data based on values mentioned in the list ( 2 conditions only ) and if found it indicate " match " or " not match" if 2 conditons is not match.
hope its clear
thanks in advance
Compare.xlsx
0

Microsoft Excel

132K

Solutions

37K

Contributors

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.