Linux HowTo: How to swap rows by just swapping the first cells?

Original Source Link

Say I have this table:

Occasionally I need to swap and add rows to reorganize my table. In this example, I put the row Cat at the bottom, and insert row Horse:

And then later decide to move row Mouse to the bottom again, and leave row 3 empty:

For convenience I just cut/copy/paste/move/insert the names only, and expect that the program understands that what I mean is to cut/copy/paste/move/insert the whole rows. Is there a way to make it understand that?

I can use any spreadsheet software.


Edit: I realize that I can select the whole row by pressing Shift + Space. This can be the answer for now. However, I’d like to leave this question open if anyone wants to go the hard way. This would benefit everyone.

Instead of cut/copy/paste/insert etc just the name, select the entire row by clicking on the numbered row header first, then do your action to move the entire row all day once.

Tagged : / /

Linux HowTo: In Excel sheet I want to add semicolon to a cell that time format is HH MM SS

Original Source Link

Example

09::08:00

09:09:01

I want to add semicolon like this

09::08:00;

09:09:01;

enter image description here

Change the format in custom format:

enter image description here

Also you could point to a cell with the time you want using the formula:
=HOUR(A2)&”:”&MINUTE(A2)&”:”& SECOND(A2)&”;”

:Caveat:

  • The NUMBER, MILITARY TIME or even TIME written in correct format could have TWO COLON between Hours, Minutes and Seconds, and does have SEMI COLON at end also.
  • But Excel doesn’t considers the TIME entered as you have at top with the question 09::08:00 as a TIME value, also if you apply any formula then get #VALUE error.

Below are methods, applies TWO COLON in between & SEMI COLON at end.

enter image description here

  • To convert Number or Military Time, into desire format the Formula in cell L101 and M101:

    =(TIME(LEFT(K101,LEN(K101)-2),RIGHT(K101,2),0))
    

N.B. This works if & when cell has 4 digits.

  • Formula in cell L106 and M106:

    =(INT(K106/10000)&":"&INT(MOD(K106,10000)/100)&":"&MOD(K106,100))+0
    

N.B. This works with more than 4 digits.

  • Cell format applied for SEMI COLON at end.

    h:mm;

  • This may be h:mm:ss;

  • Cell format for 2 COLON in between & SEMI COLON at end.

    h::mm:ss;

  • Cell K110 has TIME value and cell format applied on cell M110 is,

    h::mm::ss;

You may adjust cell references in the formula as needed.

Tagged :

Making Game: Excel 2007 or 2010 copy/paste without merging cells & with shift all down

Original Source Link

Let say I have an excel sheet with structure like

enter image description here

I select a few cells (at one column) and click Ctrl+C. After that I want to paste it. I select one cell(B116):

enter image description here

And click Ctrl+V. It gives me an error:

enter image description here

Excel think that I need to copy just one column or merge it. But I want excel just to create a few rows and moving all content down. How can I do it?

What you are trying to do, confuses Excel. When you paste an array by selecting one cell (and the pasted area goes into a merged cell), Excel does not know how you want the information pasted into the cells. If you highlight the same number of cells as the copied array, Excel will not have a problem and will paste the information.

Tagged : / / /

Linux HowTo: How to pivot (transpose) data sets of differing length

Original Source Link

I have 5000+ records in Column A. Each record contains a number of rows of data. The number of rows containing data varies from record to record. How do I transpose the data in these records so that each record occupies only one row? See included image. (using Excel included in Microsoft 365)

enter image description here

Combination of IFERROR & INDEX command, and few helper value solves the issue.

::Caveat::

  • Shown method works with any version of Excel.
  • It’s not an smart method but solves the issue.
  • To improvise this exercise, readers may suggest
    an efficient Formula/Macro.

enter image description here

How it works:

1. Insert series of numbers (I93:M93) based on maximum elements per record set, here maximum is 5.

2. Enter another series of numbers (H94:H96) based on record set, here are 3.

3. Formula in cell I94:

 =IF($H94=1,IFERROR(INDEX($I$78:$I$82,I$93,1),""),IF($H94=2,IFERROR(INDEX($I$84:$I$86,I$93,1),""),IF($H94=3,IFERROR(INDEX($I$88:$I$92,I$93,1),""),"")))

4, Fill it across.

Adjust cell references in the formula as needed.


::Edited::

Since OP is handling huge data set (around 5000 records) therefore VBA Macro can a smart way to handle the issue.

Sub TransposeWithBlanks()
 Dim Data_Array
 Dim OutPut_Array()
 Dim LR As Long, Counter As Long, LR2 As Long
 Dim i As Long

 Application.ScreenUpdating = False

  With Sheets("Sheet1")
     LR = .Cells(Rows.Count, "A").End(xlUp).Row + 1
     Data_Array = .Range("A1:A" & LR).Value2
  End With


    On Error Resume Next
    For i = LBound(Data_Array, 1) To UBound(Data_Array, 1)

       If Trim(Data_Array(i, 1)) <> vbNullString Then
            Counter = Counter + 1

            ReDim Preserve OutPut_Array(1 To 1, 1 To Counter)

            OutPut_Array(1, Counter) = Data_Array(i, 1)

        Else

            With Sheets("Sheet1")
                LR2 = .Cells(Rows.Count, "C").End(xlUp).Row
                .Range("C" & LR2 + 1).Resize(1, Counter).Value2 = OutPut_Array
            End With

            Counter = 0
        End If
    Next i

End Sub

enter image description here

N.B.

  • Save the workbook as Macro Enabled.
  • In Above code Sheet name and data range are editable.
  • If you want to Paste the Transposed data in another Sheet, then you need to edit this portion of the code.

    With Sheets("Sheet1")
                LR2 = .Cells(Rows.Count, "C").End(xlUp).Row
                .Range("C" & LR2 + 1).Resize(1, Counter).Value2 = OutPut_Array
            End With
    

that is very kind of you to look at this challenge. While your formula works, it expects to be told the cell range for each individual record ($I$84:$I$86 for Record 2…then $I$88:$I$92 for Record 3… and so on. With 5000 records, one would need to input the ranges of each of the 5000 individual records – which is not really practical.

I am not an excel guru, so please excuse my following ‘layman’s’ thoughts.

The formula (or more likely macro?) that I think would work is as follows (using your table above):
1) Instruct the macro to search downwards in column I for the word (string?) “Record”.
2) Each time the macro finds the word “Record” instruct it to copy/paste the data in the subsequent cells immediately below, horizontally into the adjacent cells (to its right) – and stop the copy/paste when it reaches the next cell down with another word “record”.

As I said, I don’t know how to ‘program’ formulas or macros in excel – so my suggestion may be totally not possible.

Anyhow, regardless, you have been very kind to look at this. I hope someone has come across this problem before, and has a workable answer.

Yours kindly, Sean

Tagged : / /

Making Game: Excel 2007 or 2010 copy/paste without merging cells & with shift all down

Original Source Link

Let say I have an excel sheet with structure like

enter image description here

I select a few cells (at one column) and click Ctrl+C. After that I want to paste it. I select one cell(B116):

enter image description here

And click Ctrl+V. It gives me an error:

enter image description here

Excel think that I need to copy just one column or merge it. But I want excel just to create a few rows and moving all content down. How can I do it?

What you are trying to do, confuses Excel. When you paste an array by selecting one cell (and the pasted area goes into a merged cell), Excel does not know how you want the information pasted into the cells. If you highlight the same number of cells as the copied array, Excel will not have a problem and will paste the information.

Tagged : / / /

Linux HowTo: Separate text at variable words in Excel

Original Source Link

I want to separate Text in a columns in two separate columns.

The columns should separated after following variable Performance,Learning & Growth and Business.

| AM                                          | AN                | AO                     |
|---------------------------------------------|-------------------|------------------------|
| Performance       comment                   | Performance       | comment                |
| Learning & Growth        some comment       | Learning & Growth | some comment           |
| Business       This is third comment        | Business          | This is third comment  |
| Performance          This is fourth comment | Performance       | This is fourth comment |

I tried Data Tools > Text to Columns. But this isn’t working in Learning & Growth row.

Thank you for your time and consideration

Edit:
I am able to split Upper Text of comment using below formula

=TRIM(LEFT(AM2, SEARCH(CHAR(10),AM2,1)-1)))

But I am not able to split Lower Text

=RIGHT(AM2,LEN(AN2)-FIND(" ",AM2))

This is giving me Value Error

HYG a way to do it with forumlas:

  1. Replace any space within your variables with an underscore, hash, or
    any unique character. Example: the variable “Learning & Growth”
    should be “Learning_&_Growth”
  2. This can be done by CTRL + H > in Find what box type “Learning
    & Growth”
    , and in Replace with box type
    “Learning_&_Growth”.
  3. Assuming text to be split is in column A. Put the below formula in
    an adjacent column

=LEFT(A1, SEARCH(" ",A1,1)-1)

  1. Put the below formula in the next adjacent column

=RIGHT(A1,(LEN(A1)-FIND(" ",A1)))

  1. The 1st formula will extract the variable name, as it extracts any
    characters before the 1st space.

  2. The 2nd formula will extract the text after the variable name as
    required.

Tagged : / /

Making Game: How to swap rows by just swapping the first cells?

Original Source Link

Say I have this table:

Occasionally I need to swap and add rows to reorganize my table. In this example, I put the row Cat at the bottom, and insert row Horse:

And then later decide to move row Mouse to the bottom again, and leave row 3 empty:

For convenience I just cut/copy/paste/move/insert the names only, and expect that the program understands that what I mean is to cut/copy/paste/move/insert the whole rows. Is there a way to make it understand that?

I can use any spreadsheet software.


Edit: I realize that I can select the whole row by pressing Shift + Space. This can be the answer for now. However, I’d like to leave this question open if anyone wants to go the hard way. This would benefit everyone.

Instead of cut/copy/paste/insert etc just the name, select the entire row by clicking on the numbered row header first, then do your action to move the entire row all day once.

Tagged : / /

Making Game: Separate text at variable words in Excel

Original Source Link

I want to separate Text in a columns in two separate columns.

The columns should separated after following variable Performance,Learning & Growth and Business.

| AM                                          | AN                | AO                     |
|---------------------------------------------|-------------------|------------------------|
| Performance       comment                   | Performance       | comment                |
| Learning & Growth        some comment       | Learning & Growth | some comment           |
| Business       This is third comment        | Business          | This is third comment  |
| Performance          This is fourth comment | Performance       | This is fourth comment |

I tried Data Tools > Text to Columns. But this isn’t working in Learning & Growth row.

Thank you for your time and consideration

Edit:
I am able to split Upper Text of comment using below formula

=TRIM(LEFT(AM2, SEARCH(CHAR(10),AM2,1)-1)))

But I am not able to split Lower Text

=RIGHT(AM2,LEN(AN2)-FIND(" ",AM2))

This is giving me Value Error

HYG a way to do it with forumlas:

  1. Replace any space within your variables with an underscore, hash, or
    any unique character. Example: the variable “Learning & Growth”
    should be “Learning_&_Growth”
  2. This can be done by CTRL + H > in Find what box type “Learning
    & Growth”
    , and in Replace with box type
    “Learning_&_Growth”.
  3. Assuming text to be split is in column A. Put the below formula in
    an adjacent column

=LEFT(A1, SEARCH(" ",A1,1)-1)

  1. Put the below formula in the next adjacent column

=RIGHT(A1,(LEN(A1)-FIND(" ",A1)))

  1. The 1st formula will extract the variable name, as it extracts any
    characters before the 1st space.

  2. The 2nd formula will extract the text after the variable name as
    required.

Tagged : / /

Linux HowTo: Is there Something like a Dark Background Excel Theme?

Original Source Link

Is there an easy way to apply a dark theme to my excel windows: black or gray background on all cells, light text.

Ideally, this would be just a theme/view that could be applied, rather than actually changing the colors in the document itself.

Yes there is. This can be done in both Excel 2007 & 2010. I have 2010, so to change it go to

FILE > OPTIONS > GENERAL

These options are available there;

enter image description here

I would do as @CharlieRB suggests and additionally set a small background image, any color, or image that works for you. You can make this a default template as well if you like. Not sure how printing will work for you, but you could set a custom view to deal with printing, if that is an issue.

Page Layout>Background
enter image description here

Create and select a small image

enter image description here

enter image description here

ok so i just figured out a solution, but it comes with drawbacks. you can use windows aero to change the default color of all programs which will do exactly what you’re asking for, but will affect most standard programs as well. so here’s what you do:

  1. right click on desktop
  2. select ‘personalize’
  3. select ‘window color’ button
  4. select ‘advanced appearance settings’
  5. in the box that opens look for a box that says ‘active window’
  6. click in the white space of that box
  7. just below said box it will have a drop-down menu titled ‘Color 1’
  8. select this drop-down menu and choose any color you’d like

This will change the background color of excel, word, etc. Hope this helps.

So, this is not my answer and I found this ‘workaround’ from a reddit user u/towndown8. He posted this image, and I am posting the link. Hopefully, it helps people as it helped me.

https://imgur.com/nSekHZX

this is the original thread I got it from:
https://www.reddit.com/r/excel/comments/b9fo97/dark_mode_question_how_to_get_the_worksheet_to/

Office 365 now has dark mode for Mojave OS on Mac. It just came out today for the Insiders Fast track updates. I’ve been looking forward to this for years.

I don’t prefer the insider’s updates, because I don’t want to test Excel for Microsoft. Also, the updates come too often, disrupting my work flow.

Can’t wait!

I have managed to get my Excel, Word and Notepad to all show light gray background by default by changing the Windows Theme, which works well for me. I have windows 10 and Office 2016 and have been struggling with this for a long time. I had to create a new theme and change the Window Color to gray.

Tagged :

Linux HowTo: Hiding a column from a pivot table without removing it from the chart

Original Source Link

I have a pivot table with two columns: number of users who visited a website (impressions) and number of users who registered on the site (regs). The rows are for dates.
I want to visualize the percentage of users who registered after visiting the site. Thus, I have the number of users for each cell as a value field, displaying it as percentage of impressions. Generating a pivot chart from the table, impressions and regs are plotted over date as a percentage of impressions. This means there is one line at 100% for impressions (always 100% of itself) and the graph for registrations below that.
I’d like to remove the line for impressions, but when I set a filter to do so, registrations vanish as well, since the column for impressions is filtered from the pivot chart as well, turning the value field invalid.
How can I just show registrations as a percentage of impressions in the chart?

I think your title has what you want backwards. Your description sounds like you want to hide data from the chart but not from the table.

You could:

  1. Make a regular chart from the pivot table data, which includes only some of the pivot data. Updating the chart will fail if the pivot table changes size.

  2. Hide the unwanted series in the pivot chart (no markers and lines, or no borders and fills).

  3. Exclude the unwanted data from the pivot table.

  4. Make a second pivot table with just the data you want to show in the chart.

Tagged : / /