EXCEL CONCATENATE/FORMULA/SCRIPT/MACRO Data manipulation

• Project ID:

  632075

• Project Type:

  Fixed

• Budget:

  $30-$250 USD
  

Project Description:

We use EXCEL 2003. I think this task requires someone with a good knowledge of EXCEL formulae and probably be able to produce scripts/macros to do what we need.

We have a basic inventory system which needs product titles to be generated from our product model details. We want these titles put together in groups.
In a nutshell I want:

COLUMN A.............COLUMN B........COLUMN C Etc
PARTNUMBER1..........MODEL1..........DETAIL1
PARTNUMBER1..........MODEL2..........DETAIL2
PARTNUMBER1..........MODEL3..........DETAIL3
PARTNUMBER2..........MODEL3..........DETAIL3

all to be joined together for items of "PARTNUMBER1" to form MODEL1 DETAIL1 MODEL2 DETAIL2 MODEL3 DETAIL3, but we have several limitations and changes that must be done along the

way, including

adding a fixed piece of text to the end of each title,
a maximum number of characters to be shown per title (any words that go over this length are to be used in the next title),
and duplicates to be omitted from certain columns

For products with PARTNUMBER2 MODEL1 the Title must start again since PARTNUMBER2 is a different product altogether. The same process must then be followed.

We have a list of 15,000 model details, across about 150 'PARTNUMBER' entries, so need this to be able to handle a lot of data.


I would like to be able to contact the designer on MSN and really we want this done ASAP (start tonight!?)


I have uploaded a sample sheet so you can take a look at the kind of thing we are looking for, and below I have written a more detailed description of the way I see what what we

need which I hope makes sense! These points are straight from my head so may not in the right order for the order in which the data is processed!

This entire process only needs to apply to PART TYPE "BAT" and the process needs to be reset for each PART #

1 - FROM Worksheet 'CORE'; Join All of:
JOIN1..........JOIN2..........JOIN3..........JOIN4..........JOIN5 together
(Concatenate?) and insert a space in between each word. Any double spaces need to be converted to single spaces. This should give the output:

JOIN1 JOIN2 JOIN3 JOIN4 JOIN5 (if there is text in each field)
JOIN1 JOIN3 JOIN4 JOIN5 (if there is text in all fields but JOIN2 left empty, for example)

The 'grouped' titles must have a maximum character length (inc spaces) Which is set out in the 'Settings' tab. When this length is reached then a new 'group' title must be created

on the next row below, and it must begin from 'JOIN1 again. Text/Numbers must not be truncated (i.e Rabbit cannot be left as 'Rab' and then 'bit' be the start of the next grouped

title)


EXAMPLE:

PART TYPE.....PART #..........JOIN1..........JOIN2..........JOIN3..........JOIN4..........JOIN5
BAT.......................001..........MANU1 ..........SERIESA..........A..........10..........
BAT.......................001..........MANU1..........SERIESA..........A..........11..........
BAT.......................001..........MANU1..........SERIESA..........A..........12..........
BAT.......................001..........MANU1..........SERIESA..........A..........13..........
BAT.......................001..........MANU1..........SERIESA..........B..........10..........
BAT.......................001..........MANU2..........SERIESA..........B..........15..........
BAT.......................002..........MANU1..........SERIESA..........A..........17..........


All JOIN fields need to be added together for all BAT 001 items.

If the length limit was set at 43 characters I would expect the following groups:

"MANU1 SERIESA A 10 MANU1 SERIESA A"
"MANU1 SERIESA A 10 MANU1 SERIESA A 11 MANU1"
"MANU1 SERIESA A 12 MANU1 SERIESA A 13 MANU1"
"MANU1 SERIESA B 10 MANU2 SERIESA B 15"

Start process again for BAT 002

"MANU1 SERIESA A 17....etc"

To make this more complicated....:
The "output" group must have any duplicate cell contents from JOIN1 JOIN2 JOIN3 JOIN5 (NOT JOIN4) removed, so the final ouput we would expect from the above would be:

"MANU1 SERIESA A 10 11 12 13 B 10 MANU2 15"
(this is under the 43 character limit)

If the rule for this was lengrth limit of 38 characters it would give:
"MANU1 SERIESA A 10 11 12 13 B 10"
"MANU2 SERIESA B 15"
(too much content for the group so started new group)


Once all of the above has been done, the output 'group' must have the text from 'TITLE-PART-2, B3' from 'Settings' Worksheet CONCATENATED to it, to give us our final title which

we will use on our website.

Skills required:

Data Processing, Excel, Microsoft, Visual Basic

Additional Files:

Freelancer-Excel-CORE.gif Freelancer-Excel-SETTINGS.gif Experiment.xls Explanation.txt