VBA Help Please (rookie issue)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Thought I had this figured out, but I am scratching my head...

I have three columns that have a mixture of information in them, and I need
to offset certain types of information. The issue I am dealing with is that
sometimes my 2nd column contains quantity, sometimes contains markings, and
other times contains a part number. Good news is that each is formatted
consistantly based on what type of information it is.

Quantity is of course always a number
Markings are always in bold text
Part numbers are always begin with all caps

Here is an example of what I have now:

ColA ColB ColC ColD
1 Panel
HP <--bold text
AB123

Here is what I need to end up with:

ColA ColB ColC ColD
1 Panel HP <--bold text
1 AB123 HP <--bold text

Any help you can provide would be greatly appreciated.

Thanks,

Scott
 
Scott Wagner said:
Thought I had this figured out, but I am scratching my head...

I have three columns that have a mixture of information in them, and I
need
to offset certain types of information. The issue I am dealing with is
that
sometimes my 2nd column contains quantity, sometimes contains markings,
and
other times contains a part number. Good news is that each is formatted
consistantly based on what type of information it is.

Quantity is of course always a number
Markings are always in bold text
Part numbers are always begin with all caps

Here is an example of what I have now:

ColA ColB ColC ColD
1 Panel
HP <--bold text
AB123

Here is what I need to end up with:

ColA ColB ColC ColD
1 Panel HP <--bold text
1 AB123 HP <--bold text

Any help you can provide would be greatly appreciated.

Thanks,

Scott
Click to expand...
 
I can see that the "1', the "Panel", and the "HP" all go in one row because
that is what you have in the "end up with" group.
I can also see that the "1", the "AB123", and the HP go in another row,
again because that is what you have.
What I can't see is the logic of how you go from what you "have now" to "end
up with".
For Excel to differentiate numbers from bold text from part numbers is not a
problem. The problem is knowing what to do with them. Please post back
with details of the logic involved. HTH Otto
 
Background: In the example the first line is a basic description of a
product assembly (panel). It consists of two pieces, the panel and the AB123
is mounting hardware. Together they make up one assembly designated "HP".

The file I start with is produced by a vendor product selection program, and
it arranges the parts and desciptions in the way I described below in an
output excel file. The sales people in my company have to rearrange the
listing manually now and then paste the resulting data into our order system.
Today this is a long process since this can be 10's or even 100's of items
on any given order.

Definitions in examples:

1 = Qty of Panel to be ordered
Panel = Component description
AB123 = Part number (first two characters upper case)
HP = Marking/designation (bold text)

The good news is that these items are always in the same place to start
with, that is if there is a designation.

Here is the logic:

IF ColB is bold text,
THEN copy contents to ColD down one row
AND copy contents to ColD up one row
AND delete entire row.
END IF

IF ColB is first two characters upper case,
THEN move contents to ColC same row
AND copy contents from one cell above in ColB one row down
END IF

Thanks Otto!
 
Scott
I see the logic of what you want from what you have. You say that there
are many items in any one order and therefore that many cycles that your
sales people have to go through for just one order.
What I don't see yet is how the data in your Excel file (that you get
from the product selection program) is arranged as a whole. In other words,
is all the data for one item all in one sheet (4 entries)? Or do you get
many sub-ranges of 4 entries in one sheet and your sales people pick the
items ordered from this sheet?
What I am getting at is, again, the logic Excel will have to use to
determine what entries go to make up one item, and how is the group of
entries for one item separated from those for the next item.
For instance, can your sales person select a range of cells that contain
all the entries for the desired item and then click on a button to rearrange
the entries. Or are you looking for Excel to rearrange all the entries for
many items in one go?
Please be patient. Of the two of us, you are the only one who knows
what you want. HTH Otto
 
Otto,

First, I certainly appreciate your questions and attention to detail, no
impatience on my part at all. Thank you for taking the time to help me.

To answer your questions:

Q: Is all the data for one item all in one sheet (4 entries)?

A: Yes, all data is in the same sheet. I just realized that I didn't
mention the purpose of column A. This column contains a line number, and
appears only on the first row of a new product. This could be used to
identifying the starting point for each product. Below is an example of how
this looks

What I have now:
Item No. Qty Part Designation
1 1 Panel
HP
AB123
2 1 Panel
HQ
AB123
3 1 Panel
HR
AB123

What I want to end up with:
Item No. Qty Part Designation
1 1 Panel HP
1 AB123 HP
2 1 Panel HQ
1 AB123 HQ
3 1 Panel HR
1 AB123 HR


Q: Can your sales person select a range of
cells that contain all the entries for the desired item and then click on a
button to rearrange the entries. Or are you looking for Excel to rearrange
all the entries for many items in one go?

A: The sales people will be running a macro from a menu item on the entire
sheet, not selecting a specific range. I actually have about 90% of the
total macro built that formats the sheet for many other items/configuration
items to translate the file we get from the product selector software into
the final format we need to paste into our order system. Actuallly I thought
I had this project finished and I was thrown a curve ball, the issue we have
been discussing comes up with one product type, and I expect it may occur
with others. I am taking a step backward now to make the
distinctions/seperations discussed so far. This element will fit into a
framework already in place. Let me know if you need more information on
this, it's difficult to explain.
 
I think we have it nailed down now. It would help me if you send me, direct
via email, a small file with a sample of the data as you get it from your
source. If you feel the data is proprietary just make up some data. I just
want the layout and a few examples of what you have. My email address is
(e-mail address removed). Remove the "nop" from this address. Otto
 
Back
Top