hi
I made a sheet but it is very time consuming. I have to take daily 24 corresponding values per day on hourly basis.
I am requesting you once please go though this and provide the easiest way to solve the problem because i have to make 200 more than sheets which is not possible to make by doing filter, copy & paste.
With regards,
Arun
You have provided two workbooks. It is not clear from your explanation nor the worksheets what you wish to accomplish. Please explain to us in simple language exactly what you want. We are not mind readers and cannot discern your desires from the information provided. Explain your issue and what you are looking for to us as if we were standing in line together to get at a coffee shop.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Hi,
Please provide corresponding data on hourly basis from raw data.
Previous sheets are the results.
I am attaching the raw data sheet with this.
Hi
I am attaching raw data with this comment and also attached result sheet with name PR-CH_Jehanabad.
with regards,
arun
I repeat my request to you.
I am not prepared to review pages of data and try and figure out what you want. Provide a simple explanation with examples. We do not need lots of data, but only a small sample with a complete explanation.
Please Login or Register to view this content.
I need a formula which will select data automatically from raw data sheet considering to near by (Corresponding) every hour duration. There should be not adding or average of one hour duration data.e.g:-
if i have following data :-
Date & time Value
6/30/2015 23:55 4
6/30/2015 23:35 3
6/30/2015 23:19 6
6/30/2015 22:59 5
6/30/2015 22:44 8
6/30/2015 22:24 3
6/30/2015 22:09 8
The result should be in another sheet :-
6/30/2015 24:00 4
6/30/2015 23:00 5
6/30/2015 22:00 8
I need only 24 readings per day on hour basis from raw data without adding or averaging at that duration data. The Problem is raw data is in random form i.e we are getting 2 data sets/hour,3 data sets/hour,4 data sets/hour so i am not able to pick alternate values or nth value in sheet.
I want to develop a sheet in which i want to paste data in raw data sheet i will get result data in dashboard sheet.
Request you to look into previous sheet PR-CH_Jehanabad.xlsx i have attached, you can see first sheet is the result.
with regards,
Arun
1. Using your raw data sheet. keep your data sheets segregated by days so that only one day's data is on a sheet. Resort the data so that the order is earliest in the day to latest in the day.
2. In column F, type this formula: =mod(E1,1) and copy it down. Then copy all the data in Column E and paste it special in Column E over the existing data as values. Then reformat it as a number with three decimal places.
3. In column G type 1:00 in G1, 2:00 in G2, etc down to 24:00. Then reformat these 24 cells to a number with three decimal places
4. In column H, type =INDEX($B$1:$B$2116,MATCH($G$1,$F$1:$F$2116,1)) and copy down.
Dear sir,
I did as you suggest bu i get #N/A
It is probably because the times are in descending order versus ascending order. I had that issue until I put them in ascending order. This is the best I can do for you. It worked for me with your spreadsheet. Double check your steps. I can offer no further help as this worked with your data.
Dear sir,
Can you send me the sheet this formula
Unable to do so. I never saved it once I got it to work. You will have to crunch through the steps again. Make sure that you only have one day of data on each sheet.
Dear Sir,
I did but same error is coming again & again . Test sheet is attached.
with regards,
Arun
Remove $ sign from $G$1
H1=INDEX($B$1:$B$2116,MATCH(G1,$F$1:$F$2116,1))
Try this and copy towards down
Your time starting with 6:23:45 so that from 1:00:00 to 6:00:00 the result will shows error.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Thanks sir,
Its working for me .
Thanks a lot.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks