Number generation

[AJHANSEN777]

Afternoon,

I am trying to generate a drawing number through excel. There are 9 sections for generating each unique number within the final drawing number. So section one would be the first digit in the drawing number, 2nd section would be the second digit in the drawing number and so on.

Each section has a list of parts and I have labelled each part 1-7. So in section when I put in a quantity of one within the part number and the said part number is labelled 2 then the drawing number would start with a 2. I'd work my way through each section and then hopefully have a drawing number 9 digits long (123456789).

Is there a way of generating this on Excel?

Andy

[Aardigspook]

It's a bit tricky without seeing an example sheet, but try something like this:

Some assumptions:
- Your part numbers are just numbers (i.e. '1', '2', etc - not something like '1-top', '2-left', etc)
- The part numbers are in A1, B1, C1, D1, E1, F1, G1
- You want the answer in A2

In A2, put this:
=A1&B1&C1&D1&E1&F1&G1

If the first assumption is wrong, then the formula will need amending. I'm travelling shortly, so will be off the forum, but someone else on here will be able to help if that's needed - or I'll be back on tomorrow pm.

[Kaper]

Sounds it is possible, as you havejust single digits in each section(and don't have 0 in first section) you can get a number by concatenating all partial values, like

Please Login or Register  to view this content.

[AJHANSEN777]

I've tried to upload and example spreadsheet but nothings happening.

A1=part number
B1=Quantity
C1=Unique number in section
D1=Final Unique number

This is section one put a quantity in B4 which has a unique number of 3. The number 3 would then would be visible in D4

that is section one complete and i'd continue to do that for the next section. Eventually down the bottom in column D I will have a cell that displays the final drawing number.

so you would have in column D 9 digits.

What formula would I need to put in the cell the final drawing number?

[Kaper]

Let's try with attachment:

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

upload and close

remember to keep file size reasonable.

[AJHANSEN777]

example.xlsx

Kaper,

See attached.

[newdoverman]

Perhaps this will work for you.
Enter this formula in a place convenient to calculate the number from the values in column E and fill across. This is an Array formula that is entered with Ctrl+Shift+Enter. For the example, I entered this in A100

Please Login or Register  to view this content.

Enter this formula in E101

Please Login or Register  to view this content.

[Kaper]

Similar, but a bit different aproach:
Assuming column E is empty:
1) Select 9 cells in one column (say range A102:A110).
2) Having this 9 cells selected enter the formula and commit it with Ctrl+Shift+Enter - it is array forrmula. Important note - not enter array formula to one cell and copy down. do as I wrote - select all 9 cells and then enter one formula for 9 cells.

Please Login or Register  to view this content.

This way you have all 9 digits listed starting from position of singles, to tens, hundreds, ...
3) And in E101 standard formula:

Please Login or Register  to view this content.

Note that first argument is 10^{0,1,2... so it is {1,10,100,...