Hey guru's,
I'm trying to create a list of string variables (can contain both numbers and letters) into a single column with a defined number of values for each. Can anyone help, this should be so easy! Thanks!
EX: 3 of each
x
100
100a
100a
aaa
aaa
aaa
Answer:
100
100
100
100a
100a
100a
aaa
aaa
aaa
Interesting....this should be so easy
Is there no way to do this then? Does no one have an idea?
I'm driving myself insane with index's and match's that arn't working.
Hi,
Sorry - I don't quite understand how you arrived at your answers based on your example - can you explain?
Regards
I'm sure there is a way, but without actually seeing a sample of how the data is structured, and what you would actually like to see as a result, it's very hard to give you a precise answer, INDEX/MATCH seems a natural, but it would depend on how the data is set up...
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
ExampleFile(1).xlsx
Here's the example file. What I want esentially is to add all the values from B to all the values in column A, even if those points don't exist in the original file.
As an exta the values in C need to take from the next closest real point above, unless the first point is missing then it needs to take from below. This also means the values in B need to stay in the correct order.
I know this makes the question more difficult. I was assuming I could use another function to do a COUNTA on my B column, but I might as well give you the whole problem.
Thanks so much, its been wrecking me all day.
Edit: Oh and I planned on using some VLOOKUP's to grab values, so that's not a big deal, I just cant set it all up right with formulas.
Last edited by S.Taylor; 11-06-2013 at 06:06 PM.
Forgive me - it's been a while since I've come across a post where I have to confess that I have absolutely no idea what is being asked for, and I only hope that this is due to ignorance on my part rather than to any lack of explanation on yours.
Regards
Ok, Column A is well names. These can have both numbers and letters in the names.
Column B is subsurface interval picks. Column B picks will always be in the same order. These can have both numbers and letters in the names.
Column C is the depth that the pick is found at.
I'm trying to make fake picks where they have not yet been picked in each of the wells.
I'm sure It's all on my part that the confussion is coming from. I just can't give out the real file so I had to make a fake example for you. Did that clear it up at all?
I think I see what you are getting at, BUT, what determines the number of rows for each A column entry? the first value in the list? the value in A column with the most entries? an independent "needs to have this" list?
from what I am seeing, it looks like "if any value has a B column entry, all unique Entries in A column need to show an entry for that as well?
I'm just a little confused too...
I think you've got it. You can look at it like this, which might clear it up.
Forget about column C
Pretend you have Baskets that all contain an apple an orange and a banana, but the file you are given only has some of those fruit
Basket_1 Orange
Basket_1 Banana
Basket_2 Apple
Basket_2 Banana
Basket_3 Apple
Basket_3 Orange
Basket_3 Banana
I need a file that will have 3 rows for each basket, each row with the respective fruit.
This means that the number of rows are defined by the number of fruit total for each basket. (in this case 3 per basket)
Does that help, I know that might sound ridiculous but I just want it to be as clear as possible.
I think this one may take a bit, I'm still not sure where the # of entries comes from, if it's from the data supplied, then a unique list of column B entries needs to created first, and if the first Item does not contain one of those entries, where does it get it's value from?
The number of entries are the # of unique values in Column B. I can supply a list in a separate column though so thats not a big deal. I just would like for it to be reusable so the number of entries could vary and it would still populate fine.
You do have a set number you need (but they could be variable), and the undefined values get assigned from previous data....
not sure what this is about, but I do not believe I can participate any longer, the results desired are not understandable to me( Either there is a master list or there is not, I want all categories in master list or not) not sure what I asked wrong, but I do not see me going any further with this, sorry
SimpleSheet.xlsx
Ok, Here's the most simple example possible. Forget about making it usable over and over. Can you find a way to do it here? The reason why I don't want to copy and paste is that in practice there could be 10000 baskets.
Let me know if this will work or if you really can't tackle this. Perhaps I confused thing saying I want it to be re-usable, but it's actually crazy simple.
I think I may finally have worked out what you're trying to do. Based on your last attachment with the Baskets and Fruits, both of these are array formulas:
In F2 and copy down:
=IFERROR(INDEX($A$2:$A$10,SMALL(IF(FREQUENCY(MATCH($A$2:$A$10,$A$2:$A$10,0),ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10))+1),ROW($A$2:$A$10)-MIN(ROW($A$2:$A$10))+1),ROUNDUP(ROWS($1:1)/SUMPRODUCT(1/COUNTIF($B$2:$B$10,$B$2:$B$10)),0))),"")
In G2 and copy down:
=IFERROR(INDEX($B$2:$B$10,SMALL(IF(FREQUENCY(MATCH($B$2:$B$10,$B$2:$B$10,0),ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1),ROW($B$2:$B$10)-MIN(ROW($B$2:$B$10))+1),IFERROR(1/(1/(MOD(ROWS($1:1),ROUNDUP(SUMPRODUCT(1/COUNTIF($B$2:$B$10,$B$2:$B$10)),0)))),ROUNDUP(SUMPRODUCT(1/COUNTIF($B$2:$B$10,$B$2:$B$10)),0)))),"")
Regards
@XOR LX you're a hero. That's amazing.
My only follow up question would be how could I make the $A$10's and $B$10's variable. Right now If I add values to A or B it wont take them into account. IT also wont output any values if I drag those to be larger than the number of values I have. I'll need some sort of a command to take a look at the number of filled cells in A or B and fill in that value in those equations, rather than having a hardcoded number of responses.
Ah I also noticed that the fruit are in the wrong order if it doesn't see them in the right order first. As it stands you would have to put a fake basket with all the fruit first so it could recognize the order.
Thnaks again though, I never would have been able to get this far. You're a beauty.
Last edited by S.Taylor; 11-07-2013 at 11:27 AM.
No worries.
Best thing to do now is to create a couple of dynamic Named Ranges and use those in the formulae so that they will respond automatically to any additions/deletions in the data.
Go to Name Manager and create two new names, viz:
Name: Basket_Range
Refers to: =Sheet1!$A$2:INDEX(Sheet1!$A:$A,MATCH(REPT("z",255),Sheet1!$A:$A))
Name: Fruit_Range
Refers to: =Sheet1!$B$2:INDEX(Sheet1!$B:$B,MATCH(REPT("z",255),Sheet1!$B:$B))
Your (array) formulas now become (I've made a small modification to one as well):
In F2:
=IFERROR(INDEX(Basket_Range,SMALL(IF(FREQUENCY(MATCH(Basket_Range,Basket_Range,0),ROW(Basket_Range)-MIN(ROW(Basket_Range))+1),ROW(Basket_Range)-MIN(ROW(Basket_Range))+1),ROUNDUP(ROWS($1:1)/SUMPRODUCT(1/COUNTIF(Fruit_Range,Fruit_Range)),0))),"")
In G2:
=IF(ROWS($1:1)>PRODUCT(SUMPRODUCT(1/COUNTIF(Fruit_Range,Fruit_Range)),SUMPRODUCT(1/COUNTIF(Basket_Range,Basket_Range))),"",INDEX(Fruit_Range,SMALL(IF(FREQUENCY(MATCH(Fruit_Range,Fruit_Range,0),ROW(Fruit_Range)-MIN(ROW(Fruit_Range))+1),ROW(Fruit_Range)-MIN(ROW(Fruit_Range))+1),IFERROR(1/(1/(MOD(ROWS($1:1),ROUNDUP(SUMPRODUCT(1/COUNTIF(Fruit_Range,Fruit_Range)),0)))),ROUNDUP(SUMPRODUCT(1/COUNTIF(Fruit_Range,Fruit_Range)),0)))))
You can copy down as far as you like (blanks will only result where there is no data to pick up) and these will now respond automatically to any changes to the data in those columns.
Note: the Named Range formulas depend on the entries in both those columns being textual (which they are in your example) and may not give the desired results if they contain numerical entries.
Regards
Yeah, I tried that already but I will unfortunately have entires for both a and b which are numbers, letters and random characters. I'm wondering if I could somehow use a count command just to get the $A$Name_range.
I also have the issue, you may not have seen it aboive with your quick reply, that if the fruit arnt in the right order for the first basket it wont put them in the right order later... hmm...
One other question: is there also the possibility of blanks within either range? If not, detecting the last non-blank cell in each range is obviously made easier.
Regards
No there shouldn't be any blanks within the range. So that makes it easy.
The other point is if you think of apple being 1 and assigning a number to each fruit going down the master list.
The numbers assigned to the fruit have to be in the right order moving down the new 'G' column. It works if the forumla finds the right fruit first in B but if some fruit are skipped until the last basket and should come ahead of apple, it wont recognize that. I know its tricky, but thats why I figured a master list might be an easier way to go than querying the B column. If we could just paste the master list over and over in a function it would work just fine.
Ok, I can make the ranges detect the last cell in case of mixed entries, no problem.
Not sure about this other point, though, I'm afraid: where is the order specified? How is it determined? Can you explain what you mean?
Regards
Are you able to re-upload the Basket/Fruit sheet with an illustration of this, i.e. where the order is not as you would expect?
Regards
SimpleSheet(2).xlsx
Here's the new example. I added a tomoato which is missing from the first 3 baskets. You can see how your formula will drop tomato to the bottom of the fruit list rather than 2nd as you see in the master list. Column B will be in the correct order, but it's missing fruit which causes the problem.
So would you be happy simply using this Master List then? I just presumed that this was an extraction of unique items from column A (and so would have to be determined anyway), and didn't realise there was an order to them.
Alternatively, if there is a logic behind the order of unique items to be extracted from column A, let me know and I can write it into the formula(s).
Either way is fine (though obviously the first should be easier) - let me know.
Regards
Lets go the easy route and simply use the master list. As long as it can account for both numbers and letters it should work just fine (along with the fact I could have 10000 rows to go through and all those arrays would crawl)
Agreed!
Ok, so using your last sheet, do away with the Fruit_Range Named Range and use these two instead (modify the upper limit of 10,000 if you feel that will be too low):
Name: Basket_Range
Refers to: =Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$10000,MATCH(TRUE,INDEX(Sheet1!$A$2:$A$10000="",,),0)-1)
Name: Master_List
Refers to: =Sheet1!$D$2:INDEX(Sheet1!$D$2:$D$10000,MATCH(TRUE,INDEX(Sheet1!$D$2:$D$10000="",,),0)-1)
Formulas now:
In F2:
=IFERROR(INDEX(Basket_Range,SMALL(IF(FREQUENCY(MATCH(Basket_Range,Basket_Range,0),ROW(Basket_Range)-MIN(ROW(Basket_Range))+1),ROW(Basket_Range)-MIN(ROW(Basket_Range))+1),ROUNDUP(ROWS($1:1)/COUNTA(Master_List),0))),"")
In G2:
=IF(ROWS($1:1)>PRODUCT(SUMPRODUCT(1/COUNTIF(Basket_Range,Basket_Range)),COUNTA(Master_List)),"",INDEX(Master_List,IFERROR(1/(1/(MOD(ROWS($1:1),COUNTA(Master_List)))),COUNTA(Master_List))))
Do some testing and let me know.
Regards
It works! The only issue I have is that it runs super slow since im dealing with ~5000 rows right now. It locks up for about a half hour whenever I make changes. In the end I think it will do though so again, thanks you so much! I couldn't have done it without you.
Any tips on making excel calculate faster? haha I doubt it can be helped.
I'll set it to solved and you've already got a rep up from me!
Hi,
Well, for a start, you could switch to Manual Calculation mode whilst you're making changes in the sheet, so that it doesn't constantly recalculate (when you don't really want it to).
I always work in this mode, though a lot of advanced users will advise against it, especially if you're not used to it; the main reason being that you have to constantly remember that you are in Manual Calculation mode, and therefore that any changes you make in the sheet will have no effect until you force a re-calculation.
Also, since there are a few repeated strings in the formulas, we can take those out into a single cell and have the formulas reference those cells instead, which will mean each of the 5,000+ rows will not have to (re-)perform the calculation at each step.
For example, if you put, in H2, say:
=PRODUCT(SUMPRODUCT(1/COUNTIF(Basket_Range,Basket_Range)),COUNTA(Master_List))
And in H3:
=COUNTA(Master_List)
Then your formulas become:
In F2:
=IFERROR(INDEX(Basket_Range,SMALL(IF(FREQUENCY(MATCH(Basket_Range,Basket_Range,0),ROW(Basket_Range)-MIN(ROW(Basket_Range))+1),ROW(Basket_Range)-MIN(ROW(Basket_Range))+1),ROUNDUP(ROWS($1:1)/$H$3,0))),"")
In G2:
=IF(ROWS($1:1)>$H$2,"",INDEX(Master_List,IFERROR(1/(1/(MOD(ROWS($1:1),$H$3))),$H$3)))
This won't necessarily produce a massive improvement in speed, though it should help a little. You could also make sure you're not copying these formulas down to a row which is greatly beyond that which is actually required (even if they only result in blanks in those cases, they are still nevertheless calculating).
If you're still not happy with the performance level, you may have to consider going for a VBA solution.
Regards
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks