Find average of similar rows and write into different excel


#1

Hi,
I want to calculate the average of similar rows and write it into different excel sheet.
Please help


#2

find the rows counts,then calculate average based on the average values set the range in excel read and write range to read and write excel


#3

pls share xaml…its urgent


#4

@somya177 please find below file

avg.zip (9.9 KB)


#5

but it is Writing the common name values in sheet2 name column.
I want it only once after calculating average of similar rows.


#6

in your excel there are two excel columns but in mine there are 3…name,number and date…
BOT is Writing average value in date column of sheet 2…it should write in 4rd column of sheet2…i.e. average column


#7

@somya177 can i have sample data and expected output?


#8

Sheet1 is input and sheet2 is the required output…
please help…its urgent

Latest.xlsx (8.9 KB)


#9

@somya177 check the below file. Output is in sheet3

avg.zip (10.7 KB)


#10

Hi,
its working fine for giving average velues but its giving date in wrong way…it should be 2018/07/15 but it’s giving as 2018/00/15
is it beacuse few dates are written as 2018-07-15 and few as 2018/07/15 in sheet1?
if this is the case then how can we rectify it??


#11

@somya177 can i have sample excel sheet with both date formats.?


#12

Sheet1 is input and sheet2 is the required output…
please help…its urgentLatest.xlsx (10.1 KB)


#14

@somya177 try below code. In formats array variable add all formats which are present in your excel sheet code will work.

Main.xaml (20.1 KB)


#15

it is giving date as 01/01/0001…it is supposed to give 2018-07-15


#16

@somya177 your output format should be MM/dd/yyyy right?


#17

yes in mm/dd/yyyy


#18

@somya177 did you add all date formats in your excel to formats array variable?


#19

yes…


#20

@somya177 can i have screenshot of formats array variable values


#21

Hi @Manjuts90,
below is the format variable of variable type- string []
and Default- {“MM/dd/yyyy”,“dd/MM/yyyy”,“yyyy-mm-dd”}