LinkBack URL
About LinkBacks
Here,
Here I want to get data in master sheet in C column.The unique data of master sheet of C column should be extracted from remaining three sheets which are in different columns.
Pls find enclosed workbook.
With Best Rgs
Here,
Here I want to get data in master sheet in C column.The unique data of master sheet of C column should be extracted from remaining three sheets which are in different columns.
Pls find enclosed workbook.
With Best Rgs
I thought that this would take 2 minutes.
Not quite...
It was a bit more complicated than I'd thought. It's done in a few stages (as shown in the file) and involves the use of Array formulas. These need to be set with CTRL + SHIFT + ENTER and not just enter. Don't type the curly brackets. Excel will do that. If they don't appear, hit F2 and try again.
Any problems, just ask.
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
Dear Sir,
Thanx for the reply.I think I could not get into the combining part formula.I have tried in one of my original workbook as enclosed 4 sheets with but fails in case of combining unique part and ultimately result part.Pls note that the data is very large.The combining part is in XFC column and helper column in XFD column and Result in Q column and from XEY to XFB are 4 sheets ( earlier in above it was 3 only).
Hope you can resolve this issues.
I think that I can fix this. But first, please delete the 20,000+ columns that you have added and hidden!! When I try to delete them my PC crashes. Once you've done that - please re-post your sheet. You can hide columns without the need to add tens of thousands of empty columns!!
Last edited by Glenn Kennedy; 12-21-2014 at 12:55 PM.
OK. Plan B... I used a different PC and was able to get it to work, even with the 20,000 empty columns!!
there were two problems. I don't know what you had done to the helper column (XFB), but it wasn't right. It is now. the second problem was caused by the 20,000 columns. I had to adjust the formulas to copy with the HUGE number of additional columns. This will not help processing time. Can't you delete the empty columns and then just hide the columns with formulas in a rather more conventional way (Home/Format/Hide & Unhide)? Anyhow, have a play with it & see what you think.
Dear Sir,
Thnx for the your kind effort.My data is large and it goes on increasing.Let me see,if I can do anything else to minimise.
Dear Sir,
The columns can be unhidden.Since there was a lot of data in different columns,so I have deleted those and hide the conventional way of hinding.I do not know the other way of hiding.Secondly,I have a lot of data and a lot of sheets which currently in above I have deleted and I mostly get stumbled in processing time taking a bit longer as I have to deal with a lot of large data.
Thus, a formula
=IFERROR(INDIRECT("Details!"&TEXT(SMALL(IF(Details!$XEX$4:$XFA$2000<>"",ROW(Details!$XEX$4:$XFA$2000)*10^6+COLUMN(Details!$XEX$4:$XFA$2000)),ROWS($A$1:A1)),"R000000C000000"),0),"")
can you elaborate in detail.
In the meantime my problem is solved.But I would prefer that how could I minimise the processing time.Also,the range define 2000 rows is just a sample,it goes on increasing day by day as the data goes on feeding.How to then adjust the range if it is >2000 rows.Should I take 3000 or 4000 and adjust the formula every now & then or what else ?.Kindly let me know your suggestions.
With Best Rgds,
Suresh
The range was set to 2000, merely as a sample. you should re-set it to a value that suits you. One thing that you can do is set the calculation option to Manual. then the sheet will only recalculate when you tell it to do so (after you enter F9). That will stop the sheet recalculating everything when you are entering updated data. Just remember to hit F9 t update the tables when the new data are entered.
the formula is looking at the array of cells in XEX-XFA, finding the non-blank cells and assembling them into a single column: to enable INDEX-MATCH in the result column to work. I mislabelled the column "uniques" - they aren't as you can easily see. if you wish, rename the column Single column array.
Thnx for the reply.
Also what does *10^6 in formula it means ?
If someone helps me the same calculation with vba code would it speed up or not.
With Best Rgds,
Suresh
Ten to the power of 6
I mean to say what does it mean in the formula.*10^6.
I have already added reputation to you.
Thanks for the reputation. it's a way of identifytng where the missing cells are in the array (e.g. a4), returningFALSE where there are gaps and then assembling th edata froma 2D array into a 1D array, omitting any gaps. Check it out on this MUCH smaller sample and go to Formulas/Evaluate formula/evaluate to see each step in action.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks