1 Finish Profile

Excel data format problem

Locked

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.

Topic

Excel data format problem

In reply to Excel data format problem

Point value changed by question poster.

Excel data format problem

In reply to Excel data format problem

Point value changed by question poster.

Excel data format problem

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

Excel data format problem

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

Excel data format problem

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

Excel data format problem

In reply to Excel data format problem

Poster rated this answer

Excel data format problem

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

Excel data format problem

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

Excel data format problem

In reply to Excel data format problem

Poster rated this answer

Excel data format problem

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

Excel data format problem

In reply to Excel data format problem

Poster rated this answer

Excel data format problem

In reply to Excel data format problem

This question was closed by the author

[Author]

Replies