Community archive

Ask something
Discussion Locked

Excel data format problem

0 helpful
12 replies

by john.mcknight

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

12 replies

by don christner

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

by john.mcknight

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

by don christner

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

by john.mcknight

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

by md_hashim

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

by abarnes70

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

This discussion is closed to new replies.