General discussion

Locked

Excel data format problem

By john.mcknight ·
The problem is how do i convert the following sample data (a1:b12):

Part # desc

521007 a
521007 b
521007 c
521007 d
521007 e
521007 f
521008 1
521008 2
521009 dd
521009 ff

to the following (a1:b5):

Part # desc

521007 a b c de f
521008 1 2
521009 dd ff

I need to concatenate the description but thats the easy part as each part number may have a varying number of descriptions ie rows.

I am open minded to any solution utilising access or excel.

Thanks.

This conversation is currently closed to new comments.

12 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Excel data format problem

by john.mcknight In reply to Excel data format problem

Point value changed by question poster.

Collapse -

Excel data format problem

by john.mcknight In reply to Excel data format problem

Point value changed by question poster.

Collapse -

Excel data format problem

by Don Christner In reply to Excel data format problem

If you bring it into Access you can write field1 to a memory variable, then do a loop while the memory variable equals field1, concatenating field2 with each pass. Then move to the next field1 and loop again until done.

Don

Collapse -

Excel data format problem

by john.mcknight In reply to Excel data format problem

Thanks for the idea, however your solution is beyond my level of Access ability, if you could be any more specific i'd appreciate it, if not i'll persue your idea with an in house techie.

thanks

Collapse -

Excel data format problem

by Don Christner In reply to Excel data format problem

If you bring it into Access you can write field1 to a memory variable, then do a loop while the memory variable equals field1, concatenating field2 with each pass. Then move to the next field1 and loop again until done.

Don

Collapse -

Excel data format problem

by john.mcknight In reply to Excel data format problem

Poster rated this answer

Collapse -

Excel data format problem

by john.mcknight In reply to Excel data format problem

Thanks for the idea, however your solution is beyond my level of Access ability, if you could be any more specific i'd appreciate it, if not i'll persue your idea with an in house techie.

thanks

Collapse -

Excel data format problem

by md_hashim In reply to Excel data format problem

What I'd do is split the data into 2 columns using formulae - split, left & right - then use if to concatenate the data
something like:
=IF(LEFT(A1,6)=LEFT(A2,6), CONCATENATE(LEFT(A1,6),RIGHT(A1,1), RIGHT(A2,1)), )
Cheers,
Hashim

Collapse -

Excel data format problem

by john.mcknight In reply to Excel data format problem

Poster rated this answer

Collapse -

Excel data format problem

by a.barnes In reply to Excel data format problem

I would use an Excel macro to do this. The basic algorithm would look something like:

1. Sort the data by Column A
2. Copy product code at A1 to another location, eg D1
3. Copy description at B1 to adjacent cell, ie E1
4. Go down 1 row. If A2 = A(2-1) then concatenate B2 to E1, otherwise create a new product code at D2, and copy the description B2 to E2.
5. Keep repeating until the end of the data is reached (you could prompt for the number of rows).

I hope that's clear...if not shoot me an email and I could send you some sample code.

Good luck!

Andrew

Back to Community Forum
12 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums