Excel Help and Problems

[Something Fishy]

Are they unique in anyway? If yes,simply sort by them and delete rest.

If not, make them unique by adding a column of numbers, count 1-100, then repeat this again. Then auto complete down should give you a sets of 1-100.

It's a list of coordinates, but I don't need such a high level of precision. Your second suggestion worked great, thanks.

[aygart]

It's a list of coordinates, but I don't need such a high level of precision. Your second suggestion worked great, thanks.

Alternatively you can enter references into some other cells.

=a1
=a100
=a200
=a300
etc.

[etech0]

Alternatively you can enter references into some other cells.

=a1
=a100
=a200
=a300
etc.

You could even use INDIRECT to get the reference and then copy the formula down

Example: (assuming the data you want to copy is in Col B and you are working in Col A)
=INDIRECT("B"&ROW(A1)*100)

[yitzf]

Another way:

=MOD(ROW(A1),100)=0

You'll get true or false, just filter for true (or filter for false and delete those rows).

[etech0]

Another way:

=MOD(ROW(A1),100)=0

You'll get true or false, just filter for true (or filter for false and delete those rows).

Another great solution!

While we're at it, you can do this with VBA too!

[skyguy918]

While we're at it, you can do this with VBA too!

Reminds me of my internship, when my boss had me convert a new mortality table into life expectancies using native excel functions, VBA, and APL separately. Had to base my end of summer presentation on a short 1 week side project, because saying you did the same thing 3 different ways kind of makes it sound like you didn't accomplish anything all summer.

[aygart]

Sometimes I get spreadsheets with entire columns or dollar amounts formatted as text. Even after selecting the column and changing the format to currency it only actually changes it if the cell is edited by placing the cursor in the edit field. Is there better way to do this or a way to do it automatically in one shot?

[churnbabychurn]

Sometimes I get spreadsheets with entire columns or dollar amounts formatted as text. Even after selecting the column and changing the format to currency it only actually changes it if the cell is edited by placing the cursor in the edit field. Is there better way to do this or a way to do it automatically in one shot?

Yes, u need to do text to column​ trick.

It refreshes whole column.

(I do this all the time so I recorded it as a macro.. looking for a relative reference macro though... Didn't have time to do it.)

[aygart]

Yes, u need to do text to column​ trick.

It refreshes whole column.

(I do this all the time so I recorded it as a macro.. looking for a relative reference macro though... Didn't have time to do it.)

What is the trick?

[churnbabychurn]

What is the trick?

Text to columns, (option in data ribbon ) but don't select anything ( tab, space etc) as the deliminator, so it all stays in same column.

[yitzf]

Sometimes I get spreadsheets with entire columns or dollar amounts formatted as text. Even after selecting the column and changing the format to currency it only actually changes it if the cell is edited by placing the cursor in the edit field. Is there better way to do this or a way to do it automatically in one shot?

I'm pretty sure you can use a macro too.
Here's what I use for hardcoding formulas, it should work for this also. (select cells first - macro will run on all selected cells)

Code: [Select]

Sub Hardcode()

Application.Calculation = xlCalculationManual

    Dim rngCell As Range

    On Error GoTo e
       
    For Each rngCell In ActiveWindow.Selection
       
        rngCell.Value = rngCell.Value
       
    Next rngCell
   
Application.Calculation = xlCalculationAutomatic

    Exit Sub

e:
Application.Calculation = xlCalculationAutomatic
End Sub

[churnbabychurn]

I'm pretty sure you can use a macro too.
Here's what I use for hardcoding formulas, it should work for this also. (select cells first - macro will run on all selected cells)

Code: [Select]

Sub Hardcode()

Application.Calculation = xlCalculationManual

    Dim rngCell As Range

    On Error GoTo e
       
    For Each rngCell In ActiveWindow.Selection
       
        rngCell.Value = rngCell.Value
       
    Next rngCell
   
Application.Calculation = xlCalculationAutomatic

    Exit Sub

e:
Application.Calculation = xlCalculationAutomatic
End Sub

Lol, I just copy and paste as values.

 (Select,left click border, drag and drop etc)

[yitzf]

Lol, I just copy and paste as values.

 (Select,left click border, drag and drop etc)

Mine's faster. I have the macro in an addin and added to my QAT, so I just select, hit F9 and done.

[churnbabychurn]

Mine's faster. I have the macro in an addin and added to my QAT, so I just select, hit F9 and done.

Making the macro, adding it, making sure it doesn't screw up your life (many macros do ime) etc

[Zalc]

Making the macro, adding it, making sure it doesn't screw up your life (many macros do ime) etc

I didn't want to use a macro on the QAT either, so what I did was add the "Paste as Value" button to the Quick access toolbar in the 1st position (needs to be added from settings).

Now I have an easy shortcut: Alt + 1 (or whatever position it is on the QAT).

Bonus is that it works great if I want to copy the value only to a new sheet.

Same goes for pretty much any function that I use often, they end up as a shortcut in the QAT, and I don't need to remember my keyboard shortcuts as excel shows you a hint every time you press just ALT.

[aygart]

Lol, I just copy and paste as values.

 (Select,left click border, drag and drop etc)

THat would require a place to paste to and is numerous manual steps each time. When you are dealing with a large number of sheets which have a column in the middle of many which needs to be switched it becomes a big pain.

[churnbabychurn]

THat would require a place to paste to and is numerous manual steps each time. When you are dealing with a large number of sheets which have a column in the middle of many which needs to be switched it becomes a big pain.

U paste in place

[aygart]

U paste in place

By dragging and dropping?

[churnbabychurn]

By dragging and dropping?

Yeah drag n drop in place. Can also CTR and paste in place... takes me less than 1.5 seconds to do

[aygart]

Yeah drag n drop in place. Can also CTR and paste in place... takes me less than 1.5 seconds to do

dunno, pasting in place ids not working by me.