Help with sorting data

[milesy]

Guys

i have a list in excel that has a column (A) that changes and the data is listed in column E that contains all the values.

I wish to pull all the data from E into one cell (concatenate) for a given A value.

(ive attached a pic to make it clearer)

Any pointers for doing this??

excelhelp.jpg

[humdingaling]

column e is missing in the picture
confused on what you are actually trying to achieve

can you provide a sample spreadsheet (sanitized from private data of course) with before and after to give better understanding

[milesy]

sorry caused confusion

anyway i have attached a before and after sheet....before.jpg

after2.jpg

[humdingaling]

i can only think of a very long if statement to do the job and even then i would need to know how much data you are looking at

[wenqq3]

E2 =

Please Login or Register  to view this content.

Is this okie?If not, please provide a sample workbook. Thanks

Edit:
Please drag down to the last row of your data. Then copy and paste as value. before delete the empty row of Column A.

[humdingaling]

amended the code slightly as the code did not work when two consecutive lines with no space

Please Login or Register  to view this content.

works for all but the last one where you just need to amend one cell of extra ,

[milesy]

thanks hundinaling

this comes close to working - but not quite....i modified to read

IF(A2="",IF(A3="",CONCATENATE(E2,",",E3),E2),IF(A3<>"",E2,CONCATENATE(E2,",",E3)))

and i get a good result for the lines where there is either 1 or two entries.......however when there is 3 or more lines i only get the result for two of the entries such as the attached work book

ideally all the results for a given A column value would appear in the first line of the A column.....

[wenqq3]

Please Login or Register  to view this content.

hI milesy,
You should do like this.

[milesy]

mmmmmm not quite

if i change the last F3 from above to E3 it works - again to a point

for anything that contains more than 2 responses (from the attached workbook above look at line 20 as an example there are 14 values attributable to this line....also when there are blank lines (say in line 21 ) then i still get a response from the formula

perhaps the better was is something that will extend the values in column A ....as in if it can populate the value of A1 in A2 until the value of A changes........that way i can pull in all the variables from column E using column A as a reference?

[wenqq3]

G800000355 QT720 Bottom PCB(edit).xls

Hi milesy,
Do is the col F are the answer you need?

[milesy]

that works perfectly

so now i just sort for column A and my results should line up with the A value required

so what was wrong previously

[wenqq3]

As mention at post#8.Because you are copy col D value, that why it only show two. The formula is copy cell below (Col F) if col A is blank.