Hi all,
I have an excel spreadsheet with around 840 rows, each row has several columns followed by a 'count' column. For example, there are 500 (count) people aged 1 (age) in the year 2000 (year) in region 7 (region).
Is there a way to expand each row to actually represent the 'count' i.e. expand the first row to 577 times, the next to X amount of times it says count for and so on?
I hope i've explained this in a way that makes sense!
Kind regards
Yes it makes sense.but it will make easy for those who may want to help you, if you upload a workbook with sample data (Do not included 840 rows,,,maximum 20 rows would be enough for applying a solution on).Data must be desensitized in the sample WB.
Post a WORKBOOK. Nobody wants to type data from a picture or paste text from your post into a spreadsheet as a prelude to helping. To attach a file, scroll down to the Manage Attachments button, browse to the required file, and then push the Upload button.
Teach me Excel VBA
Yep. With this array formula:
=IFERROR(INDEX(A$2:A$4,MATCH(0,--(COUNTIF(F$1:F1,A$2:A$4)=$D$2:$D$4),0)),"")
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
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...
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
Thank you! I have attached an example file here!
Last edited by cbroadhurst; 04-22-2017 at 08:40 AM.
My effort will accommodate that OK. It might be a tad slow to run, but it'll get there...
Thanks Glenn, I'm very new to this. How do I add an additional column before the count column? In addition, how do i expand the yellow box to accommodate my many individuals!
Thank you for your help
How also do i align the columns in the yellow box?
Desperate for help
Be patient. You are not paying us for a service. We do this for fun and I have a life, too.
it took about 40 seconds for Excel to calculate 7000 rows, using the formula in the attached sheet.
Last edited by AliGW; 03-27-2018 at 12:48 AM. Reason: Attachment replaced with desensitised version.
I know sorry if i appeared rude! Thank you again you have been such a great helpHave a great weekend!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks