Sid29

1

votes
2

answer
23

views

Pandas - DataFrame Convert column or Pivoting Column into a new row

I have a dataframe like this, EmpID FirstName LastName Relationship FirstName.1 LastName.1 Relationship.1 1 Ax Bx 1A Cx Dx 1B My excel source file didn't have .1 on the duplicate columns but pandas created it - when I read i...
Sid29
3

votes
3

answer
26

views

Python - Check if a value in a df1 column is present in df2 column

I have two dataframes, df1 ID Key 1 A 2 B 3 C 4 D df2 ID Key 1 D 2 C 3 B 4 E Now, if the key in the df1 is found in df2 then the new column will have a value found else not found the df1 with the output dataframe becomes, ID Key Result 1 A Not Found 2 B...
Sid29
5

votes
4

answer
231

views

Parallel loading of Input Files in Pandas Dataframe

I have a Requirement, where I have three Input files and need to load them inside the Pandas Data Frame, before merging two of the files into one single Data Frame. The File extension always changes, it could be .txt one time and .xlsx or .csv another time. How Can I run this process parallel, in or...
Sid29
1

votes
3

answer
34

views

Hide Worksheet on the basis of the ending part of their name - VBA

I have a large number of worksheets inside my workbook. I only need to make worksheets that has the name "Main" and tabs that are ending with "_Summary". [ Note the Underscore before the Summary ] I tried a basic logic before going to the tough part , just to hide the Main worksheet and another wor...
Sid29
1

votes
4

answer
2.7k

views

VBA - Check if “Comma” exists in a string or not

I am looking to see if a string has a comma or not. Lets say I have two usernames "David , Boon" and "David Blind". I need to write an if loop based on the conditions whether ',' exists in the User name or not. Is there a way to check that ? Something like Contains in .Net Kindly share your thoughts...
Sid29
2

votes
1

answer
53

views

Power BI - load *.htm file saved as .xls

I have a requirement where I have to import an .xls file which is saved as .*htm, .*html Opening the file and clicking on saving as, will usually show the format that the file is in and this is how it looks - I clicked on Binary and it showed me the .xls file and I opened it as Excel workbook, csv,...
Sid29
2

votes
1

answer
60

views

Replace or substitute using DAX or Query Editor - Power BI

I have a requirement where I have to loop through each character in a text field For example: a#123456; 12341 becomes 123456;12341 a123456 12341bd becomes 123456;12341 a2017d 12341ds becomes 12341 a123456/12341bd becomes 123456;12341 n/a becomes (empty) Basically I am cleaning my data here:...
Sid29
3

votes
3

answer
43

views

Replace a value by a value another data-frame

I have two data frames: Data Frame 1: ColA ColB Mat Student Por Yes Data Frame 2: ColA ColB Mat 0 Student 1 Por 2 Yes 3 No 4 And I want to create a new data frame by replacing the values in the data frame 1 by data-frame 2 Expected output: ColA ColB 0 1 2...
Sid29
3

votes
2

answer
409

views

Sorting a Vector by ascending order

I have a vector like this - MyVector
Sid29
1

votes
4

answer
43

views

R- Changing the Values of a dataframe depending on a condition

I have numeric values in a dataframe on each columns and I want to change the value of those columns. For example if a value is less than 100 then it has to be 0 and if it is more than 100 then it has to be 1. data
Sid29
2

votes
1

answer
103

views

Pivot table calculated item produces error

I have two different worksheets (Input1 and Input2) and I am writing a piece of code to build a report. My Code runs successfully on Input1 - worksheet but not on Input 2 - Worksheet. This is my data - INPUT 1 Model Date Product Payment Mode Blackberry 1/31/2010 Watch Card...
Sid29
1

votes
1

answer
353

views

How to build a Correlation Matrix in R splitted by categories?

I have a dataframe which has 4 variables - cur, price, volume and carat. Except the cut everything is a numerical variable: I am able to get the correlation matrix for price, volume and carat. Here is the below code, Jewels_Input_Data_2
Sid29
3

votes
3

answer
93

views

Pivoting tables in a pandas dataframe

I have a requirement where in I am trying to count the values and Put them in the pivot table. This is my dataframe, Cola Colb Apple Rippened Orange Rippened Apple UnRippened Mango UnRippened I want the Output to be like this, Rippened UnRippened Apple 1...
Sid29
1

votes
1

answer
27

views

Copying only formats from a cell to other cells

I am trying to copy the formats from a cell into two other cells. .ActiveSheet.Range("C" & SelRowNumber).Select .Selection.Copy .ActiveSheet.Range("A" & SelRowNumber & ":B" & SelRowNumber).Select .Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False I...
Sid29
2

votes
3

answer
100

views

Excel VBA - Date and IF Function inside the VBA is giving an error

I have a requirement where in, I am trying to find if the date in the data is greater than or equal to the current date then it should say "YES". This is my code, RDate = Application.WorksheetFunction.if(RSDate>=Date, "YES", "NO") My error is Object doesn't support property of this method. Kindly he...
Sid29
3

votes
3

answer
86

views

Sort a collection based on the number of Characters

I have a collection called as SheetNameCol which contains Sheet Names such as Act Collection, Act, Les. I have another collection called as HeaderRowCol which contains Header Rows 7,8,9. The Sheet Names from the SheetNameCol and Header Rows from the HeaderRow are related. index SheetNameCol H...
Sid29
2

votes
2

answer
3.3k

views

VBA Open an Excel file - Dialog box changes

I am running an excel vba script, where in I am trying to open a dialog box to select an excel file and open that excel file. I try to give the path of the folder so that the end user can go directly to the folder and select the file that he wants. But, it works fine for the first time but when the...
Sid29
2

votes
2

answer
937

views

Write value to a textbox in Userform - VBA

I am trying to write a value dynamically in Textbox which is placed on a Userform. This is my code, and I am getting an error in the last line. It says object required. Sub Userform1_Display() TotalSelected = 0 With Sheets("Main").Ent_ListBox For i = 0 To .ListCount - 1 If .Selected(i) = True Then...
Sid29
2

votes
2

answer
307

views

Kaggle Digit Recognizer Using SVM (e1071): Error in predict.svm(ret, xhold, decision.values = TRUE) : Model is empty

I am trying to solve the digit Recognizer competition in Kaggle and I run in to this error. I loaded the training data and adjusted the values of it by dividing it with the maximum pixel value which is 255. After that, I am trying to build my model. Here Goes my code, Given_Training_data
Sid29
2

votes
1

answer
36

views

Selenium Python - Webscraping Xpath Error

I am trying to Web-scrape the country names from the following page - http://hdr.undp.org/en/composite/trends I am trying to get the Xpath of the particular element. So for the first country, it appears like this - Country = driver.find_element_by_xpath("//[@id='styleSheet.css']/div/div/div/div/tabl...
Sid29
2

votes
1

answer
18

views

Excel - If the value is already present in the above rows, then its option A else option B

I am looking to check if a value is already present in the above rows of a column using an excel formula. If it is already present then the output is "yes" else it is a "No" For Example, Name Present AS No AD No AF No AS Yes AS Yes AD Yes My values are there in the column Name and...
Sid29
1

votes
1

answer
2.6k

views

Sorting by a column in MS Excel - VBA

I am trying to sort a column using VBA in Excel. This is my code, Range("B:B").Sort _ Key1:=Range("B2"), Order1:=xlAscending But, It sorts the header column as well. I didn't want the Header Column to be included in the sorting , So I tried the following code, Range("B1:B").Sort _ Key1:=Range("B2"),...
Sid29
2

votes
1

answer
28

views

Getting the Required Column Indexes from an array and adding those columns to a dataframe

I have a dataframe like this: A B C D 1 2 3 4 1 2 3 4 1 2 3 4 1 2 3 4 And I am writing a piece of code to identify what are the column indexes I need. RequiredColumns = (skb.get_support(indices=True)) Out[64]: array([ 0, 1], dtype=int64) I am going to take only those columns and add it to the new...
Sid29
1

votes
1

answer
964

views

How to automatically move the scroll bar on the listbox to show the selected item - VBA

I am selecting an Item in the Listbox dynamically. Now when the item is selected, the selected item has to be shown on the front-end of the listbox. More like the scroll-bar has to move to that item. So that the end user will get to know the item has been selected. I tried to use SetFocus but am get...
Sid29
2

votes
1

answer
2.2k

views

How do we Pass the multiple selected values of a Slicer inside DAX?

Let's say I have a table like this - [Orders] Date Amount Category 12/12/2017 100 A 12/12/2017 200 B 12/12/2017 300 C 1/1/2018 400 A 1/1/2018 500 B And I have a slicer for Category. If only one single Value is selected, then a measure like this will work CA...
Sid29
2

votes
1

answer
86

views

VBA - Loop through Recordset fails

I have a requirement where in I am looping through a record set without using EOF. But it fails to pick up the 2nd element, saying it couldn't find it in the collection. This is my code: Set rs = db.OpenRecordSet(strSQL) Debug.Print (rs(0)) 'Works Debug.Print (rs(1)) 'Says Item cannot be found But r...
Sid29
2

votes
1

answer
89

views

Power Query - Data Transformation from a single column to a whole table

I have a requirement where I have a table like this - Actual Table with 2 columns Column1 Column2 ColAValue $$ ColBValue $$ New Row ColCValue Above Row ColCValue2...
Sid29
1

votes
3

answer
1.5k

views

Array - Subscript out of range in VBA

I am trying to store the values inside an array. I am facing a problem it says subscript out of range. This is the code, Sub Trial() Dim HeaderArray() As Variant Dim HeaderValue As String Dim j As Long Dim i as Long set wk = Activeworkbook lastrow_header_Config = Wk.Sheets("Config").Cells(Rows.Count...
Sid29
1

votes
5

answer
543

views

VBA - UserForm Button (Macro) to Continue running the sub

I have a sub called as sub1() which activates the userform1 through the command userform1.show() This userform1 has a button called as continue. On clicking that button continue - A Macro called as Private Sub continuebutton() gets activated. I want to program in such a way that it redirects to the...
Sid29
1

votes
2

answer
114

views

VBA - Too Slow with With and selection Command. How Can I optimize it better to run faster? [duplicate]

This question already has an answer here: How to avoid using Select in Excel VBA 13 answers I am selecting a range and using that selection inside the With Command and as you all know, the .Selection command is going to slow down the process. What is the better way to write this code, to run it fas...
Sid29
1

votes
1

answer
31

views

VBA -Column Insertion code Optimization

I am trying to Insert a column in the Worksheet, Here Goes my code, Sheets(Currentsheetname).Range("A" & MyRow & ":B" & lastrow).Select .Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove Is there a way to run this code, a bit faster. At the moment it is...
Sid29
1

votes
4

answer
41

views

Get substrings from a string by matching characters

I am trying to extract the prices from a string list that I have. These is a sample of PriceList: PriceList = ['Weekly:$3,600.00\n\nMonthly:$15,120.00', 'Daily:$3,000.00\n\nWeekly:$8,400.00\n\nMonthly:$33,600.00', 'Daily:$1,800.00\n\nWeekend:$2200.00', 'Day:$3,600.00\n\nWeekly:$6,000.00\n\nMonthly:$...
Sid29
1

votes
2

answer
598

views

Setting the Row Height dynamically in VBA

I have a requirement, where I have to set the Row Height dynamically. For Example, the Input looks like - And I want my output to look like - The String in that line will keep changing. So, now based on what characters, I should set the row height? Thanks. Kindly share your thoughts.
Sid29
1

votes
2

answer
56

views

How to Identify Substring in a string without spaces

I am looking to check whether the substring PROD is present in the stringToday's proDuction and return values as True or False. Here the expected answer is true. How can I do this in VBA
Sid29
1

votes
1

answer
45

views

Split a String - Vba based on symbols

I have a string like this ABC$3$FG$Hand I want to split them into 4 different strings. string1 - ABC, Number-3, string3-FG, String4 - H. I am splitting the string with $ sign in the actual string.
Sid29
2

votes
1

answer
73

views

Load *.htm file saved as .xls (starting from row number 5) Using Power Query

I have to import an .xls file which is saved as .*htm, .*html. I attached a link which provides a sample file of that format. The Actual first row of the table starts from row number 5. But there are data above it. The file looks as below, The sample file. But please make sure to include some rows...
Sid29
1

votes
1

answer
219

views

Userform Input Small hyphen & Long hyphen? (✱Using symbols in Excel)

I am entering a string in a Userform in Excel-VBA from the user side of the form. I would want to know how to enter the Long Hyphen. The Small Hyphen would be Shift + - the (the minus sign button next to 0). How would you enter the Long Hyphen on the form as I am doing a string match in my VBA code...
Sid29
1

votes
1

answer
265

views

Creating a calculated table by passing a measure or parameter

I have a requirement where I have a data like this, Date Name Age 1-1-2018 A 1 2-2-2018 B 1 3-3-2018 B 1 6-6-2018 C 2 7-7-2018 B 6 I am trying to give a slicer to the user to select the required number of months from the last month. So to do that, I am using a calcula...
Sid29
1

votes
2

answer
855

views

VBA Display Collection in a message box - error

I am trying to Pop-up a message something like, "There are 3 Reports available . They are Today, Yesterday, Day before" This is the code, On Error Resume Next For Each a In MonthYear arr.Add a, a Next For i = 1 To arr.Count Cells(i, 1) = arr(i) Next MsgBox ("There are " & arr.Count & " Reports ava...
Sid29
2

votes
1

answer
12.3k

views

PowerBI - Match and Get the Value from another table (Vlookup)

I have 2 tables connected to each other through col A. I want to match the column C with Col A and get the value of Col B. For Example, Table 1 ColA ColB Colc a a1 b a b1 c c c1 a Table2 ColA ColB a a1 b b1 c c1 Now, I have already created re...
Sid29

View additional