LinkBack URL
About LinkBacks
Hi,
I am having Raw Data in one sheet and i want that data to come to the another sheet but in sequence in respect to Tower & Unit no. Enclosing the file for the reference.
Thanks
Naveen Aggarwal
Hi,
I am having Raw Data in one sheet and i want that data to come to the another sheet but in sequence in respect to Tower & Unit no. Enclosing the file for the reference.
Thanks
Naveen Aggarwal
Your sdescription is unclear. Please repost your sheet, showing manually calculated results. I assume that the formulae in the results sheet are NOT what you want. What DO you want?
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I just want to bring the raw data to another sheet but in sequence of tower & unit numbers
You haven't explained anything further. i need to see exactly what you mean by "in sequence of tower & unit numbers". That is whay I want to see some manually calculated results, not the outcome of a non-working formula.
Last edited by Glenn Kennedy; 02-20-2018 at 02:44 PM.
Hi Glenn,
I am sorry as could not give you proper explanation. Please find as below :
I am having Raw data sheet where Column A is having Tower no, Column B is Floor and Column C is for Unit no further Column are having relevant information.
As you can see that Tower no along with Floor and unit no, are not in sequence because, i will be getting this data filled through Google forms.
My Requirement,
I want to bring this data in sequence in respect to Tower, then Floor and then Unit. For example :
Tower Floor Unit No
3 2 1
11 18 6
5 12 4
7 13 3
12 11 2
14 10 5
11 4 3
1 5 2
9 14 3
Above is raw data in Column A, B & C
In other sheet I want as below
Tower Flat No
T03 T03 0201
T11 T11 1806
T05 T05 1204
T07 T07 1303
T12 T12 1102
T14 T14 1005
T11 T11 0403
T01 T01 0502
T09 T09 1403
Above Flat No is the combination of Tower, Floor and Unit no. My requirement is to make these data in sequence like, Lower Tower with Lower Floor and Lower Unit no should come first along with other relevant details.
Formula which exists in other sheet, i just wanted to combine above 3 column, however, you can delete it.
I will be having 1000 of line data in the raw sheet.
I know that my explanation is too long but probably i could make you to understand.
Thanks in advance.
Naveen Aggarwal
I have asked twice to see some manually calculated results: I had hoped to see your expected results, manually sorted into the correct order. However, you have not done so. So, this is a bit of a guess. If it is wrong, for the 3rd time, PLEASE show us the expected results in the sort order that you want.
In A2:
=LEFT(B2,3)
In B2 (array formula):
=IFERROR("T"&TEXT(SMALL(--(TEXT('Raw Data'!$A$2:$A$10,"00")&TEXT('Raw Data'!$B$2:$B$10,"00")&TEXT('Raw Data'!$C$2:$C$10,"00")),ROWS($1:1)),"0# ####"),"")
In C2, (array formula):
=IFERROR(INDEX('Raw Data'!D$2:D$10,MATCH(1,(--MID($B2,2,2)='Raw Data'!$A$2:$A$10)*(--MID($B2,5,2)='Raw Data'!$B$2:$B$10)*(--MID($B2,7,2)='Raw Data'!$C$2:$C$10),0))&"","")
In D2, copied across and down (array formula):
=IFERROR(1/(1/INDEX('Raw Data'!F$2:F$10,MATCH(1,(--MID($B2,2,2)='Raw Data'!$A$2:$A$10)*(--MID($B2,5,2)='Raw Data'!$B$2:$B$10)*(--MID($B2,7,2)='Raw Data'!$C$2:$C$10),0))),"")
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Re: Formula to bring data in sequence.
Hi Glenn,
You have made the thing accurately as i wanted to have.
Thanks a lot.
Naveen
Hello Glenn,
Thanks once again for the support. But could you help me once more in the same.
I am trying to extent the range as my raw data will be in 500 lines but it is not working.
Please let me know how to correct it.
Thanks in advance.
Naveen
Post the three formulae that yiu have used. If I cannot see them, I can not tell what's wrong.
Thanks Glenn,
Please find the below original formula which was suggested by you in the sheet.
=IFERROR("T"&TEXT(SMALL(--(TEXT('Raw Data'!$A$2:$A$10,"00")&TEXT('Raw Data'!$B$2:$B$10,"00")&TEXT('Raw Data'!$C$2:$C$10,"00")),ROWS($1:1)),"0# ####"),"")
Now it is working till raw 10 as the range is fixed in it. but in my raw data sheet, i have data in 1000 raws.
So, I tried to revised the formula by extending the range as below
=IFERROR("T"&TEXT(SMALL(--(TEXT('Raw Data'!$A$2:$A$1000,"00")&TEXT('Raw Data'!$B$2:$B$1000,"00")&TEXT('Raw Data'!$C$2:$C$1000,"00")),ROWS($1:1)),"0# ####"),"")
But it is not working even if i am applying it by pressing CTRL+SHIFT+ENTER
Please suggest to extent it for all the coloumn.
Thanks
Naveen Aggarwal
Last edited by Naveenaggarwal; 02-23-2018 at 04:25 AM.
I have created 1000 rows of data, using random numbers. It worked fine. To speed things up a bit (hopefully), I have adjusted the formulae that they are all non-array formulae. Without seeing your sheet, it's impossible to tell what's wrong. are there any merged cells in your raw data? Are calculation options set to automatic? can you post your sheet?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks