From multi-column Database to a single-column one.

Hello everyone.
I don't know how to make a database like this: (1.jpg), look like this one: (2.jpg).
Note that I don't simply have to transform it, but I have to leave (intact) the first one in its sheet, and have the result in another one.
Thank You.

Hi, Try this:- Results on sheet (2) Start "A1". Alter Last Line of code to suit.

Please Login or Register  to view this content.

Regards Mick

Wouldn't copy & paste work?

Originally Posted by Cheeky Charlie

Wouldn't copy & paste work?

No, because the first database is not "handwritten": is a result of another sheet. Of course, those Pictures are just simplified examples; the real sheets would be much bigger, and whith many more useless zeros.
What I need as result is a plain list of nouns with quantities attached.

Originally Posted by MickG

Hi, Try this:- Results on sheet (2) Start "A1". Alter Last Line of code to suit.

Please Login or Register  to view this content.

Regards Mick

I'm sorry but I don't understand your advice: I dont't have any programming skill.
Where, exactly, I have to write this code?

How about this?:

=IF(ROW(1:1)>COUNTA($D:$D)-COUNT($D:$D),INDEX(F:F,ROW()+COUNT($D:$D)-COUNTA($D:$D)),INDEX(D:D,ROW()))

Moved to another sheet, the references will get bigger, of course:

=IF(ROW(1:1)>COUNTA(Sheet1!$D:$D)-COUNT(Sheet1!$D:$D),INDEX(Sheet1!F:F,ROW()+COUNT(Sheet1!$D:$D)-COUNTA(Sheet1!$D:$D)),INDEX(Sheet1!D:D,ROW()))

If you were going to use a macro, I might suggest that finding the limits of the values (for example by finding 0s in the nouns columns) and simply copy-pasting the columns into the requisite places would be a lot faster than looping.

HTH

Originally Posted by Cheeky Charlie

How about this?:

=IF(ROW(1:1)>COUNTA($D:$D)-COUNT($D:$D),INDEX(F:F,ROW()+COUNT($D:$D)-COUNTA($D:$D)),INDEX(D:D,ROW()))

Moved to another sheet, the references will get bigger, of course:

=IF(ROW(1:1)>COUNTA(Sheet1!$D:$D)-COUNT(Sheet1!$D:$D),INDEX(Sheet1!F:F,ROW()+COUNT(Sheet1!$D:$D)-COUNTA(Sheet1!$D:$D)),INDEX(Sheet1!D:D,ROW()))

If you were going to use a macro, I might suggest that finding the limits of the values (for example by finding 0s in the nouns columns) and simply copy-pasting the columns into the requisite places would be a lot faster than looping.

HTH

It returns only zeros.

Hi, Open you sheet with the Basic Data,
According to your jpg file . The Data starts in cell "D3".
Right click the sheet Tab, Select "View Code":- VB Window appears.
Paste the code Below into the Window.
Close the VB Window.
To run the Code "Double Click" cell "A1".
sheet (2) starting "A1" should now show the Results.
NB:- If you want the resulkts to show elesewher, Change the Last, but one line of Code.

Please Login or Register  to view this content.

Regards Mick

Originally Posted by rudiakys

It returns only zeros.

I assumed you'd put the formula into row 3, as per your example. To be location independent:

=IF(ROW(1:1)>COUNTA($D:$D)-COUNT($D:$D),INDEX(F:F,ROW(3:3)+COUNT($D:$D)-COUNTA($D:$D)),INDEX(D:D,ROW(3:3)))

and

=IF(ROW(1:1)>COUNTA(Sheet1!$D:$D)-COUNT(Sheet1!$D:$D),INDEX(Sheet1!F:F,ROW(3:3)+COUNT(Sheet1!$D:$D)-COUNTA(Sheet1!$D:$D)),INDEX(Sheet1!D:D,ROW(3:3)))

CC