I have a table that looks like this
table.JPG
The problem is that I need to have as many rows as the value states whilst preserving the values in all the other rows, so that the table looks like this
table 2.JPG
Is there any way of doing this by a formulae or do I need to use VBA? I have no experience with VBA, so a formulae would be best.
Hi and welcome to the forum!
Rather than a couple of pictures, would it be possible for you to upload a small sample workbook, illustrating clear 'Before' and 'After' scenarios?
Regards
Sure I canExample woorkbook.xlsx
Ok,
Can you explain the significance of the one extra column in your Wanted result tab: Antall unik? It appears to consist entirely of 1s? Is this to be calculated from something?
Regards
The Antall unik(Amount unique) is calculated from Antall(Amount). I need them to be on separate rows so that I can generate unique x,y coordinates for each item(row). Did that explain?
No, sorry. Did you mean "I need them to be on separate columns"?
And why do they all show 1? And what do I need to do for this column in terms of providing a solution to you?
Am I just to ignore this column?
Regards
Ok, if it makes it easier then you can ignore this column, it can be inserted manually at a later time. The solution I need is simply to make the same amount of rows that are in Antall with all the information in the other columns to be copied.
Still a bit confused, I'm afraid.
The following line:
TS13800/81/Avslag/0-2 cm/kvartsitt/Grov
has an Antall gjenstander of 16, yet only appears a total of 14 times in your Wanted result tab - am I missing something?
Regards
My bad, I did it manually and must have missed copying it on two lines. It is supposed to be 16 times.
So, assuming you delete the extra column from your Wanted result tab for the time being, so that the columns in this tab are identical - and in the same order - to those in the Original tab, enter this array formula (please ensure you understand how to enter this type of formula in Excel) in A2 of the Wanted result tab and copy across and down as required:
=IF(ROWS($1:1)>SUM(Original!$H$2:$H$6),"",INDEX(Original!$A$2:$S$6,MATCH(TRUE,MMULT((--(ROW(Original!$H$2:$H$6)>=TRANSPOSE(ROW(Original!$H$2:$H$6)))),Original!$H$2:$H$6)>=ROWS($1:1),0),COLUMNS($A:A)))
You can copy it further down than is necessary - blanks will only result in such cases.
Regards
I tried it but got an error at ," ",INDEX. I have never used an array formula, but as far as I could google I understood that in order to enter an array formula I had to press CTRL+SHIFT+ENTER when entering the formula.
EDIT: It was the commas, my machine is set up to use semi-colon;
Last edited by Kjellis85; 11-14-2013 at 08:57 AM.
So now I tried the formula with correction of commas, but no luck. Now I get a value error, and my limited knowledge of excel makes it difficult for me to errorcheck.
On another note; if I must put this in the Wanted result sheet, then it wont solve my problem, as my problem is how to create the wanted results from the original data.
First of all, your Original tab in here is quite different from that in the attachment you first posted - you mentioned nothing there of the possibility of blank rows separating the data.
Secondly, not sure what you mean by: "as my problem is how to create the wanted results from the original data."
But this is precisely what I've given you - all you need is an extra tab ready and waiting with the headers copied over and the formulas pasted in there (which can be copied to as high a row number as necessary, or greater - as I said, only blanks will result for those rows which are beyond the maximum number to be returned).
If that's too much trouble then I can only suggest you look for an alternative VBA solution, which will 'create' the new tab for you as well as fill it with the required data.
Regards
I am sorry because I did not understand what I was supposde to do with the formula at first, but now I got it to work on the example woorkbook which I presented. However, when I tried it on the proper workbook with over 800 rows it would only return results on the first 5 rows. I don't know if there is something broken in my data, but when I expand beyond the fifth row, the result appear for a fraction of a second and then remain blank. The formula is still pasted into the cells but nothing is returned, even though the referenced sheet has data in them.
Do you have any blank rows within that data?
Have you amended all the range references accordingly to match your new situation?
If you still can't get it to work, I can only suggest you post the actual workbook. Obviously remove/replace any sensitive/confidential information in there first.
Regards
I got it working. I probably should have explained that my excel skills are very limited, and therefore I did not "understand" the formula. Now I see that the (Original!$H$2:$H$6) should be changed to the desired length of the sheet information. I think I have it working fine now, thanks for the help.
PS: for those that see this and use the array formula, be warned. Expanding it very far will consume A LOT of processing power.
You're welcome. Glad you got it to work.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks