+ Reply to Thread
Results 1 to 28 of 28

Create a defined number of copied cells for values found in a column

Hybrid View

  1. #1
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Create a defined number of copied cells for values found in a column

    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

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,603

    Re: Create a defined number of copied cells for values found in a column

    this should be so easy
    Interesting....

  3. #3
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    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.

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Create a defined number of copied cells for values found in a column

    Hi,

    Sorry - I don't quite understand how you arrived at your answers based on your example - can you explain?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Create a defined number of copied cells for values found in a column

    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

  6. #6
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    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.

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Create a defined number of copied cells for values found in a column

    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

  8. #8
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    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?

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Create a defined number of copied cells for values found in a column

    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...

  10. #10
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    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.

  11. #11
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Create a defined number of copied cells for values found in a column

    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?

  12. #12
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    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.

  13. #13
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Create a defined number of copied cells for values found in a column

    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

  14. #14
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    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.

  15. #15
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Create a defined number of copied cells for values found in a column

    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

  16. #16
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    @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.

  17. #17
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Create a defined number of copied cells for values found in a column

    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

  18. #18
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    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...

  19. #19
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Create a defined number of copied cells for values found in a column

    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

  20. #20
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    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.

  21. #21
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Create a defined number of copied cells for values found in a column

    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

  22. #22
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Create a defined number of copied cells for values found in a column

    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

  23. #23
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    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.

  24. #24
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Create a defined number of copied cells for values found in a column

    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

  25. #25
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    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)

  26. #26
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Create a defined number of copied cells for values found in a column

    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

  27. #27
    Registered User
    Join Date
    11-05-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: Create a defined number of copied cells for values found in a column

    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!

  28. #28
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Create a defined number of copied cells for values found in a column

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Check for values in a table and if found add value found in column to left to list
    By robhargreaves in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-07-2013, 02:57 PM
  2. Using a column number found using MATCH to act as the INDEX column field
    By dugwan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 07-30-2013, 07:54 PM
  3. Pasting copied cells into defined area, help!
    By lunchmeat in forum Excel General
    Replies: 4
    Last Post: 06-09-2011, 02:40 PM
  4. Add Random Number in a Column based on defined Number in another Column
    By Pack Dog in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-20-2010, 05:09 AM
  5. Replies: 4
    Last Post: 09-01-2010, 10:22 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1