Can anyone help me with this? I have a very serious problem on how can I sort specific data like Time C/In and C/Out to their respective AC-No. and NAME. What I need is that the AC-No. and its NAME must be in one row (not duplicate) and all other entries. I just need formula to come up with what I desire to happen. Thank you so much.
Please amend your sheet to show us a few samples of your desired results.
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
This formula:
=IFERROR(INDEX(A$2:A$722,MATCH(0,INDEX(COUNTIF(G$1:G1,A$2:A$722),0),0)),"")
gives you unique C and names. So, what else do you need??
You have Excel 2016. Why are you using the old Excel 2003 and earlier .xls file ?
Hello Glenn,
Thank you for your help. Partly, that's what I really need. I have attached a file with sample output that I need it to automatically sort the specific data to its respective cells and aligned to its specific AC-No and NAME.
Regarding to your question on why I am using the old Excel 2003, it is because I need this to be accessible to all versions of MS Excel. I am new to this. What I have in mind is that higher versions would possibly not compatible with the lower versions. So I am using the 2003 to make it run on 2016. I really don't know. Maybe I'm wrong.
To do this with your current date format could get messy, so I separated them out into two columns. This enabled the avoidance of array formulae, which could get slow.
I have left this in .xlsx format, so it will not work with Excel 2003 and earlier. Is this really an issue? If it is, it can be amended, but will be slower as every formula will have to be replaced by a longer (twice as long) version. This will have performance implications.
Glenn,
Thank you so much. What you have shared was awesome. I learned a lot. I studied the formula you have provided and I found out most of them which are very much useful for this file. I have added some data in columns E and H which are extracted from column C. I used some of the formulas in columns Y until AF you have provided and tried to modified them. I almost got the desired output but I really found it very hard that some data don't fall at the exact places, like that of cells AE4 and AF4, AE5 and AF5, AF7, AE8 and AF8, AE9 and AF9 and so on. They must be in order but I can hardly figure out how to do it?
I am really sorry for making this very difficult. I just really need this.
Thank you once more and more in advance.
OK. Gotcha. Select K1 to N25, as a block. Now drag across as far as needed (BJ25 for your sample data).
BtW, it's much more sensible (now) to have the time values in separate cells.
I changed the yellow highlighted column, and killed my former time column.
Yehey! you got it Glenn. But, I think 95% of the desired output is reached. As I go over with the data, I still can't figure out how these data in RED text appeared in the cells where they're not supposed to be placed. And only one from the duplicate data should reflect (in cells P8 and Q8). I have highlighted cells C214:C218, and what should appear in cell Q8 is suppose to be the time reflected in cell C218. This could be possible to happen in some other cells.
Maybe this can be fixed.
Please be patient.
Fixing a puncture. Look back later...
I see the issue. It's s complete re-write job. I'm a bit weary now and will come back to this later, or in a.m. Performance on large datasets may become an issue.
I gave up a beer o'clock meeting to get one more version tonight. these are now array formulae.
We might be able to do something about that later, but first...
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...
There are still some issues... but I think that they're "your problem" M6-P6 and the raw data C144:D147...
take another look...
It IS ABSOLUTELY beer o'clock now.
G'nite.
Glenn,
I have added 3 columns E, F, and G where G generates unique letters every change of date which serve as the ID for reference. This enables all extracted time logs to fall exactly to their respective places. I hope that what I did is correct.
Thank you so much Glenn. Hoping more from you and your kind help and collaboration to this task, next time.
MORE POWER!
Looks OK, but you keep changing your mind about whether you want all values in one cell (where they are dead text) or in separate cells. I must say, I find this output very heard to read!!
Anyway, .... you're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
My apology. Actually, it was the first option to merge all values in one cell. Its all okay if the output really is hard to read. But this is what the desired one. I added a text-wrapping to each cell which made all 5 characters fit and have them printed.
YOU ARE GREAT!...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks