In one column I have set of values(A) ranging from 40F to 140F with intervals of 0.5. I have another set of values (B) ranging from 20 to 71 with intervals of 0.1.
How to fill all values of Set B appears against each value of Set A. (sample data is enclosed).
Last edited by leo73pk; 10-27-2017 at 02:43 PM. Reason: Solved
Best Regards,
Hamza
It is the greatest of all mistakes to do nothing because you can only do little - do what you can.
The two sets in your attachment are of different lengths - what you need to show is your EXPECTED results.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
??? What is your expected answer? This is not clear to me....
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
In Set A i have temperature with intervals of 0.5. while in Set B i have various Sp. gravity with intervals of 0.1.
My requirement is to have all sp. gravity mentioned in Set B appear against each value of Set A.
e.g against temp 40 all Sp. Gravity appears
then against temp 40.5 all sp. gravity appears.......
SHOW US what you want, please. Mock it up manually and attach it here.
Do you want the results in one column e.g. 40 - 20 or in two columns?
Assuming it is 2...
=INDEX($D$4:$D$204,1+INT((ROWS($1:1)-1)/$G$3))
to return Set A, and
=INDEX($E$4:$E$5141,1+MOD((ROWS($1:1)-1),$G$3))
to return Set B. Copy down for 1,000,000+ rows...
Glenn you are right that's what I was looking forward. But the problem is its too much time consuming. moreover please note that i have to apply that formula to 204*511= over 100,000 records. I just copied to 15000 records and its hanging my core i3 machine. Is there any solution to handle this issue.
Sadly, probably not. I automatically think OFFSET with these problems. It is slow. Very very slow. INDEX will be much better. If it's no good, you will need VBA. Is that possible?? I will bow out... as i have no VBA. But others do...
I'm not much comfortable with VBA. Its fine thanks for your help. Its one time hectic activity i'm copying formula and then pasting that outputs as results.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks