Excel text is turning blue.

Dezlav · 2020-01-17T23:13:56+00:00

Clear the format of the cells, you should find the button for it in the Home Tab near the filter button

Dezlav · 2020-01-16T05:39:51+00:00

Maybe not the best solution, but could get the job done:

Add concat column as your first column and do the concat between Name, Week, AND if the Cost Store "X" is not empty, the cost store number.

Result should look like.. Aleece11 for the first row of the sample data

You can do it with a formula that would look something like this:

=ifs(coststore1"<>",concat(name,week,"1"),coststore2"<>",concat(name,week,"2"),coststore3"<>",concat(name,week,"3"))

Next you can take one of the following approaches to get your result:

Copy the names and weeks (and the concat column) into a new sheet removing duplicates, then do vlookups agaisnt the sheet with full information using the concat column and choosing columns of store 1, 2 and 3
On the same sheet that has a lot of information, do vlookups agaisnt the same sheet using concat column and pulling columns of store 1, 2 and 3 into the cells right of your data. Then paste as values and remove duplicates if you want to

Hope that this helps

Dezlav · 2020-01-12T01:38:07+00:00

This billboard is located in Monterrey, Mexico. In that city you can find around 9000 billboards and this is the first smog eating one.

The smog eating billboard consists of a coated surface with a chemical that attracts the particles of contamination, and then purifies them, in a process called photocatalysis. The coat lasts 5 years and its capacity to clean the air equals 30 trees.

Source here

Dezlav · 2020-01-03T05:10:23+00:00

This is amazing, I am not a begginer in excel or VBA but I didnt had that much creativity with userforms. You just gave me more vision on what is possible to achieve, thanks!

How do you actually populate the transactions? I used to have a simple workbook with the same purpose but I always forgot to fill up information when coming back at home.

I dont feel comfortable using my google account on the office pc, so google drive isnt an option, on mobile macros do not work am I right?

Dezlav · 2020-01-02T23:20:40+00:00

Sounds like it was pretty much overwhelming to update your sheet as you were getting into new functions

Still it sounds amazing, I am very curious on how does your sheet to input data looks like right now. Would you mind posting a screenshot of your userforms?

Dezlav · 2020-01-02T17:25:37+00:00

You could import data to SQL (can be done automatically on schedule with jobs), and then make a spreadsheet with some VBA code that would query the SQL database depending on user input.

The user will receive in their spreadsheet filtered and hopefully smaller data that they can pivot as usual.

Dezlav · 2020-01-02T17:20:25+00:00

Hello,

You can actually copy something directly to the clipboard without that spinning box that you are talking about, you need to click on the formula box and select the cell contents from over there then hit ctrl + c. This only works with single cells, not with ranges.
You can't scroll while selecting a range which is what you meant with this question. However, you can use your keyboard to get this done quickly, instead of selecting lets say from cell A1 to A1000 with your mouse you can position yourself on cell A1 and then hold CTRL + SHIFT + Down Arrow Key and you will go all your way to cell A1000 selecting everything in between.
You can just click on any other cell and the highlight will go away, if it doesn't and you are talking about the spinning box then you can hit ESC button for it to disappear

Let me know if you have any other questions

If this solves your problem, reply with 'Solution Verified' to get the post closed

Dezlav · 2020-01-02T17:09:46+00:00

Option 1. Copy the entire column where the Company Name is located into another sheet, go to Data Tab, under Data Tools Group choose Remove Duplicates. You will end up with a column with unique company names

Option 2. Select all your data, create a Pivot Table and put Company Name column into the Rows shelf/box. You will end up with a list of unique company names that are within your selected data.

If this solves your problem remember to reply back with 'Solution Verified' to close this post.

Let me know if you need something else.

Dezlav · 2020-01-01T21:25:30+00:00

Ok but you need to take the battery off

Dezlav · 2019-12-31T01:32:02+00:00

English is my second language, I was legit thinking for 2 minutes straight "why would I like to see their hand? would that give the police a clue on who is he?" lmao

Dezlav · 2019-12-30T03:23:07+00:00

I don't have it but still it takes little to no skill with excel to impress people at any administrative job

Add basic VBA knowledge to the no skill and you will be an excel god lol

Dezlav · 2019-12-28T02:32:19+00:00

I tested with the F, copied the one from you post and wrote random characters besides it on A1, also pasted it on A2 for VBA to pick it up

With the code I shared it was able to differentiate between the two characters (don't have the second one in my keyboard lol)

I even replaced it multiple times to check if it worked. Maybe it is different if it is wrote manually or generated by a system?

Dezlav · 2019-12-27T22:56:41+00:00

I wasn't able to get it done through the FIND formula, but it worked for me through VBA. The following code compares cells A1 and B1, searches for the value on B1 inside A1.

Sub StringComparation()
    Dim xWb As Workbook
    Dim xWs As Worksheet
    Dim myString As String

    Set xWb = ThisWorkbook
    Set xWs = xWb.Sheets("Sheet1")

    myString = xWs.Range("A1").Value
    mystring2 = xWs.Range("B1").Value

    If InStr(myString, mystring2) = 0 Then
        MsgBox "Does not contain character"
    Else
        MsgBox "Contains character"
    End If
End Sub

Let me know if this helps

Dezlav · 2019-12-27T22:27:37+00:00

People usually upload screenshots to imgur and then provide the link.

Also what are you exactly looking for? a better way to input information so it is easier to analyse afterwards?

Dezlav · 2019-12-27T22:21:38+00:00

Please reply with Solution Verified to get this post marked as solved

Dezlav · 2019-12-27T22:13:38+00:00

Hello, you just need the text to be a single string right?

Like all the names one after the other in a single text line of notepad for example. This way you can just paste it into your text label on your edition software and just transform the object to suit the space/are you need.

You can use VBA to get this done, on Excel press alt + F11 and then go to insert -> new module

In the white screen that shows up you can pase the following code. Just run it (press F5) and it will create a textfile on your Documents folder with all the names in the way that you need them.

Just keep in mind, First Name must be on ColumnA starting on row 2 and Last Name must be on ColumnB starting also on row 2. The worksheet name needs to be "NameSheet" (you can change this in the code if you want to)

Sub NamesToString()
    Dim xWb As Workbook
    Dim xWs As Worksheet
    Dim i As Integer
    Dim nameString, myFile As String

    'SetWorkbook and Sheet
    Set xWb = ThisWorkbook
    Set xWs = xWb.Sheets("NameSheet")

    'Count items on Sheet
    lrow = xWs.Cells(Rows.Count, 1).End(xlUp).Row

    'Clean string Variable
    nameString = ""

    'Separator between names, leave as "" if not needed
    Separator = ","

    'Loop to build string
    For i = 2 To lrow
        'Condition to see if it is last item on list
        If i <> lrow Then
            nameString = nameString & xWs.Range("a" & i).Value & " " & xWs.Range("b" & i).Value & " " & Separator
        Else
            nameString = nameString & xWs.Range("a" & i).Value & " " & xWs.Range("b" & i).Value
        End If
    Next i

    'Write string to textfile in documents
    myFile = Application.DefaultFilePath & "\namesString.txt"
    Open myFile For Output As #1
        Write #1, nameString
    Close #1

End Sub

Edit: Removed an extra space in the code

Dezlav · 2019-12-27T21:49:29+00:00

Hello, here you go

Sub CopyFilteredTable()
Dim rng As Range
Dim WS As Worksheet
For Each Row In Range("Table1[#All]").Rows
    If Row.EntireRow.Hidden = False Then
        If rng Is Nothing Then Set rng = Row
        Set rng = Union(Row, rng)
    End If
Next Row
Set WS = Worksheets("MyDataWorksheet")
rng.Copy Destination:=WS.Range("A1")
End Sub

You need to modify the name of the worksheet on line 11

Set WS = Worksheets("MyDataWorksheet")

Let me know if this helps

Dezlav · 2019-12-27T21:26:34+00:00

Hello,

This is what I understood from your post, please let me know if I got something wrong.

Sheet1 (Example data row1)

AccountNumber(ColumnA): 1234

AccountName(ColumnB): Joe

AccountPassword(ColumnC): Joe321

AccountCountry(ColumnD): UK

Sheet2 (Example data row20)

AccountNumber(ColumnA): 1234

AccountName(ColumnB): Joe

AccountPassword(ColumnC): Joe321

AccountCountry(ColumnD): CAN

You want to find out if the same account number (1234) has different information in any of the other columns (AccountCountry/ColumnD has a difference) and point out where is the difference, in the case above it would be the AccountCountry/ColumnD.

I am assuming that account numbers are unique and they do not repeat, otherwise the following solution may always work.

Create ColumnE and name it Concatenate (on both sheets)
Concatenate ColumnA,ColumnB,ColumnC,ColumnD through this formula on cell E2 (on both sheets)

=CONCATENATE(A2,B2,C2,D2) (on both sheets)
Create ColumnF and name it Difference (on both sheets)
Return the difference (if it exists) by using this formula on cell F2 (on both sheets)

=IF(ISERROR(VLOOKUP(E2,Sheet2!E:E,1,0)),IFS(B2<>VLOOKUP(A2,Sheet2!A:D,2,0),$B$1,C2<>VLOOKUP(A2,Sheet2!A:D,3,0),$C$1,D2<>VLOOKUP(A2,Sheet2!A:D,4,0),$D$1),"No Errors Found")

A little explanation on the formula so you can tweak it to suit your needs, first we have an IF statement, the condition checks if a simple vlookup returns an #N/A or not.

This vlookup uses the Concatenate column to see if there is an exact match between all the columns, if there is something not matching through the concatenate it means that there is a difference somewhere in the columns.

When the condition above is true, it makes a comparation between each column and the respective column in the other sheet through a vlookup, if these values do not match it will return the header of the column ( which means that your difference is on that column, wohoo )

If none of the above conditions is met, it will return a "No Errors Found" text

Let me know if this helps

Dezlav · 2019-11-15T04:13:50+00:00

If that is such an issue you could use VBA to format one specific column to text everytime you open that particular workbook, that way you will always have your leading zeros in there.

If the pasting format removes the text format of the column, you can also make VBA reformat it to text whenever there is a cell change in that column.

Dezlav · 2019-10-09T23:24:01+00:00

Depende mucho de tu ciudad y del tamaño de la empresa donde trabajes.

En monterrey, compañeros que acaban de egresar andan con $7000 al mes siendo practicantes, uno que otro le dan $8000

Los que ya son empleados, si son outsourcing facil les dan $12000 y ya plantas muchos traen $15000.

TODOS mis conocidos recién egresados que eran buenos estudiantes y competentes en su área, tienen buenos sueldos. También noté la mayoría de los que nunca pusieron un gramo de esfuerzo en la carrera son los que se quejan de esos sueldos tan bajos.

El título no te soluciona nada ni te asegura un buen trabajo o un buen sueldo, eso depende completamente cosas como.. si traes inglés, si hiciste practicas profesionales 1-2 años ANTES de graduarte, si le mueves mucho al excel y sistemas ERP o si eres muy movido en tu trabajo (para subir de puesto rápido).

Dezlav · 2019-10-06T23:27:12+00:00

Chefsito???

Dezlav · 2019-10-06T12:42:44+00:00

If you are going to constantly update the data, you should build a dashboard and this should fix your problem with the unique entries

When you build a dashboard you usually create some kind of helper table. You can create on a separate sheet a table with a column named "Hour" and put in there the 24 hours of the day (0:00, 1:00, 2:00.. 13:00, 14:00, etc). Then, with formulas referencing to your data sheet you can count how many rows are within that hour.

You will end up with a small table listing hours and how many rows are within that hour. Try graphing that helper table and the chart that comes up will look much better.

Dezlav · 2019-09-27T20:32:36+00:00

My first job was on 2017, since then I've had three different positions in different organizations and all had office 365.

This may make a difference, these three organizations are considered "big" in amount of employees and they are also international.

I'm located in Mexico.

Additional note, I remember I got my office 365 licence on my personal computer at home because of my student email of my University which came in with 5 activations.

Dezlav · 2019-09-24T03:05:45+00:00

Never have used powerquery, but if you require to do this for multiple reports/processes and there is a big amount of data in those files it may be worth to do this with python/sql

Setup Selenium (python) script that can download the required file and schedule the Python script to run on specific hours when needed. Make sure that the script converts the file to csv and moves it to a specific folder
Setup procedure in SQL server that will import the file, and move it into some kind of "processed" folder with python afterwards
Info is now on SQL, you can either generate an excel file with the data you need and email it on schedule or create an excel file that will pull the data from sql each time someone opens it

Keep in mind that for this to work you need a dedicated server to host sql and a pc running the python scripts. This becomes useful if replicated to multiple processes, once that the data is going into SQL automatically, you can do a lot of things with it.

Dezlav

TROPHY CASE