TRIM & Remove Duplicate and TEXTJOIN a variable range using VBA
I would like a range most varying say
A1:A20 pulled from an application with space in beginning or in end so it need to be
TRIM and then Remove Duplicates, remove blank cells and they need to be
TEXTJOIN(",",TRUE,A1:A20). Range is variable. So that I can copy paste this to QMF for running query.
Appreciate your help in creating this VBA so that I can create a button in the ribbon and can use in any excel sheet.
Sub removeDuplicate() 'removeDuplicate Macro Columns("A:A").Select ActiveSheet.Range("$A$1:$A$20").RemoveDuplicates Columns:=Array(1), _ Header:=xlNo Range("A1").Select End Sub
See also questions close to this topic
How to link a ComboBox value with a different value and put that value into a cell in a worksheet
I have an excel workbook. Within that work book I have one sheet named Classes and I have another sheet called Homework. In the Classes sheet I have the different classes in column A I also have the class hours in column B. In the Homework worksheet I have the homework assignment names in column A. I have a UserForm that has a TextBox, ComboBox and a command button. In the TextBox I am supposed to type the assignment name. The combobox is populated with the classes that are in column A of the Classes worksheet. I want to be able to type in the homework assignment and select a class within the combobox and then click the command button and this will put the homework assignment in column A of the Homework worksheet and then put the class hour (Ex. 1,2,3,4,5 not the class name) in column B of the Homework worksheet. I dont know how to link the classes that are populated in the Combobox with the class hours so that when I choose a class it will then put the class hour into a cell within the Homework worksheet.
How to prevent UiPath from getting stuck in Call Macro Stage?
I am calling a macro using call macro in UiPath Workflow. The concern is some time one of the excels that the macro handles will freeze forever, Causing the macro execution to hang and the process will get stuck in Call macro forever.
Is there some way I can modify the UiPath workflow so that, if the Call macro activity is not completed within 15 minutes, the bot throws an exception?
Scraping web with url in milliseconds Unix
The web in which I browse has several pages. I want to click on those elements or play with urls and thus be able to copy the data. The initial URL ends at = 1 & playerType = ALL & ts = 1558502019375, in my code there is a loop that is supposed to go page after page to get the data but I can not get it done.
Sub UPDATE_DATA_MLB() Application.ScreenUpdating = False 'dimension (set aside memory for) our variables Dim objIE As InternetExplorer Dim ele As Object Dim y As Integer Dim EstaPagina As Byte Dim EstaURL As String 'Página inicial EstaPagina = 1 'we will output data to excel, starting on row 1 y = 1 EstaURL = "http://mlb.mlb.com/stats/sortable.jsp#elem=%5Bobject+Object%5D&tab_level=child&click_text=Sortable+Player+hitting&game_type='R'&season=2018&season_type=ANY&league_code='MLB'§ionType=sp&statType=hitting&page=1&playerType=ALL&ts=1558502019375" '&ts=1526432697176" 'start a new browser instance Set objIE = New InternetExplorer 'make browser visible objIE.Visible = False Do Until EstaPagina = 255 'navigate to page with needed data objIE.navigate EstaURL & EstaPagina 'wait for page to load Do While objIE.Busy = True Or objIE.readyState <> 4: DoEvents: Loop 'If UCase(Left(EstaURL, 211) & "1") = UCase(Left(objIE.LocationURL, (211 + Len(EstaPagina)))) And y > 1 Then Exit Do 'look at all the 'tr' elements in the 'table' with id 'myTable', 'and evaluate each, one at a time, using 'ele' variable For Each ele In objIE.document.getElementById("datagrid").getElementsByTagName("tr") 'show the text content of 'tr' element being looked at 'Debug.Print ele.textContent 'each 'tr' (table row) element contains 4 children ('td') elements 'put text of 1st 'td' in col A Sheets("Sheet1").Range("A" & y).Value = ele.Children(0).textContent 'put text of 2nd 'td' in col B Sheets("Sheet1").Range("B" & y).Value = ele.Children(1).textContent 'put text of 3rd 'td' in col C Sheets("Sheet1").Range("C" & y).Value = ele.Children(2).textContent 'put text of 4th 'td' in col D Sheets("Sheet1").Range("D" & y).Value = ele.Children(5).textContent 'put text of 4th 'td' in col f Sheets("Sheet1").Range("E" & y).Value = ele.Children(22).textContent 'increment row counter by 1 y = y + 1 Next EstaPagina = EstaPagina + 1 Loop lobjIE.Quit Set objIE = Nothing Set ele = Nothing Range("A1").Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.RemoveDuplicates Columns:=Array(1, 2, 3, 4, 5), _ Header:=xlNo Application.ScreenUpdating = True MsgBox "Volcado terminado", vbInformation Range("A1").Select 'save the Excel workbook ActiveWorkbook.Save End Sub ```
How to find duplicates (correct way)?
I am using Snowflake database and ran this query to find total count, number of distinct records and difference:
select (select count(*) from mytable) as total_count, (select count(*) from (select distinct * from mytable)) as distinct_count, (select count(*) from mytable) - (select count(*) from (select distinct * from mytable)) as duplicate_count from mytable limit 1;
1,759,867 1,738,924 20,943 (duplicate_count)
But when try with the other approach (group ALL columns and find where count is > 1):
select count(*) from ( SELECT a, b, c, d, e, COUNT(*) FROM mytable GROUP BY a, b, c, d, e HAVING COUNT(*) > 1 )
Why there is a difference in number of duplicates? (
duplication of rows based on some conditions in large data sets
I want to duplicate a row based on condition. Like Duplicate all those rows where data$EE=1
Data set is very large and cannt be added here.
NiFi 1.9.2 version detect duplicates throws intermittent failures
Detect Duplicate is throwing intermittent failures. Error text is below>
Unable to communicate with cache when processing StandardFlowFileRecord [uuid=abcdefg,claim=StandardContentClaim[resourceClaim=StandardResoruceClaim[id=123131-21231, container=1, section=859], offset=9870383, length=39797], name=try-2019-05-21-p-2019-0521-1401, size=323] due to null: java.io.EOFException
If I retry failure the second time, it is succeeding.
I have tried changing the portnumber of Distributed Cache Service. Initially, the server for the cache service had files being persisted, but also tried to remove that setting and have data caches. Still facing issue. Also tried to increase the "Communications Timeout" from default 30 secs to 60 secs.
The configuration of Distributed MapCache Service is Server Hostname --> <one of the hosts on cluster> Server Port --> 4563 SSL Context Service ---> <verified and running context Service> Communications Timeout ---> 60 secs The configuration of Distributed MapCache Server is Port ---> 4563 Maximum Cache Entries ---> 10000 (I am only trying to check cache for max about 100 entries, so I am sure I am not hitting this threshold) Eviction Strategy --> Least Frequently Used Persistence Directory --> <Initially some path value, but now nothing> SSL Context Service --> Same verified service as Client Service>
How to TRIM OR TRUNCATE first character in DB2 column
I have column phone number with value and want to update the table column such that country code doesnt exist. 19083452345 14084456789 12023458900 18163456789
I tried to use LPAD, LTRIM but couldnt succeed.
R Winsorizing with specific cut-off values does not work
I want to winsorize my data using the mean plus (/minus) 2 standard deviations of the variable as cut-off points. I thus want to winsorize every variable one by one.
The variable I want to winsorize in the example code I provided below has 5 outliers.
I have created two benchmarks (high and low) and have inserted them in minval and maxval. Just to prevent misunderstandings: I have several timepoints and groups in my data frame, the grepl-part is to just get one group at one measurement point for the winsorizing.
My code so far:
library(DescTools) benchhigh <- mean(ds$RRS[grepl('^34.*', ds$QUESTNNR)], na.rm=TRUE) + 2*sd(ds$RRS[grepl('^34.*', ds$QUESTNNR)], na.rm=TRUE) benchlow <- mean(ds$RRS[grepl('^34.*', ds$QUESTNNR)], na.rm=TRUE) - 2*sd(ds$RRS[grepl('^34.*', ds$QUESTNNR)], na.rm=TRUE) ds$RRSout <- Winsorize( ds$RRS[ grepl('^34.*', ds$QUESTNNR) ], minval = benchlow , maxval = benchhigh, na.rm = TRUE)
The error I get is:
*tmp*, RRSout, value = c(1, 1.33333333333333, : replacement has 38 rows, data has 510"
My replacement only has 38 rows because the ^34.* group has only 38 participants. I have to winsorize the outliers per group and measurement point though...
How can I replace/winsorize the outliers of the specific participant group in the RRS variable?
Thank you very much in advance!
automatic generate list ffmpeg trimming video files
May I ask if there is an automatic way to input all the mp4 file names in the ffmpge code ? I have about 60 small video files that I need to trim, it is a pain if I have to keep entering individual file name. the mylist.txt generate only works with when concat
Join character to columnar array
I want to add a character ("~ ") to the front of each value of a columnar array, but every formula I've tried concatenates the values into a single cell rather than back to the column array. Do I need to add
SPLIT? What am I doing wrong?
This is what I've tried most recently
Ultimately, what I would like to see in a single column is:
~ Category 1 ~ Category 2
Excel TEXTJOIN with multiple IF conditions
I'm trying to use the TEXTJOIN function to create a comma separated list of the content of each row's Column D value, but only when it's Column J value reads "Regulatory" and it's Column K value reads "No".
I can get it to do this with one condition (i.e. only dependent on Column J OR K), but not both conditions (J AND K). The formula I'm using is:
=TEXTJOIN(",",TRUE,IF('Variable Database'!$J$3:$J$762="Regulatory"*('Variable Database'!$K$3:$K$762="No"),'Variable Database'!$D$3:$D$762,""))
It is a valid formula but just returns "#VALUE". Do you have any ideas about how I might achieve this?