Creating a layout of values?

[mfleming]

Hello.

Overall Situation:
From my initial 2 columns of information 1 being Panel (number) and the 2 with Reinforcing (number) create data that shows all the panels for a specific reinforcing number.

The way I do it right now is have the two columns then beside them use a formula to get the cell value next to it and fill down. Then I manualy delete the extra reinf numbers and then cut/paste panels into proper cells going from left to right.

We have to re-create panels and reinforcing numbers for everyjob so I would like to automate this.


Here is an example of what it comes out as.

http://www.homenkobuilders.com/reinf.gif

I know there is an easy way to do this but I can't figure it at the moment.

Thanks

Matt

 

[SkipVought]

Hi,

First, I don't see a DIRECT correlation.

R-02 is not 2

002 is not 2

It needs to be since you ALSO have R-03 & R-03A and LNK1. In Excel Numbers and Strings do not work well together.

This will need to be a CODE solution, so please post in VBA Visual Basic for Applications (Microsoft) Forum707.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[mfleming]

No it doesn't need anything. The "R-" is added to the NUMBER and it retains the number as is. They are ALL numbers no strings.

I tried using Pivot Tables and I almost go it. The only thing it needs is to display the panels six across for every reinf # then go down to next row.

Check it out:

http://www.homenkobuilders.com/reinf2.gif

 

[SkipVought]

"They are ALL numbers no strings."

How can that be? There are string values in the Panel Data.


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[mfleming]

Have u ever worked with Excel???

Steps for you:

1. Format Cells
2. Under Numher select Custom
3. Enter whatever you want ie. "R-"000
4. Whala you have NUMBERS not STRINGS.

Anyways, anyone who actually knows excel can help me out?

Thanks

Matt

 

[xlbo]

mfleming - firstly - if you require help, it is usually advisable to be polite to the person that is trying to help you. Secondly, I would look at Skipvought's stats on this site (click on the handle) before being so disparaging.

If you need to display across in a pivot table, then you can drag the grey 'value' box across to the right and it will do it for you. However, given that a pivot table needs a value of some description (to either SUM or AVERAGE etc), it may not work out for you. If you need the data transposed across rows, and for it to be automated, you will probably need VBA. The only other way would be to have a very large matrix of formulae, most of which would be redundant (which is pretty much what is behind older versions of pivot tables)

If you require a VBA solution, please post in forum707 where you should find ample assistnce

 

[KenWright]

Then maybe you should have explained that you applied a custom format, only we're not actually mind readers in here. We can only go by what we see, and what was seen appeared to be text which would change the solution requirements, hence Skip's observations.

Your sarcastic response was totally uncalled for, and so as you obviously know more about Excel than anyone in here and don't need any help, I'll leave you to it.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Since you are an Excel expert, I won't bore you with the details of how to apply these in conjunction with your almost-there-PivotTable.

Check out the following Functions...

OFFSET
MATCH
INDEX
COUNTA
INT
MOD
ROW
COLUMN

Using these function will give you the results you expect.


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[mfleming]

They are ALL numbers no strings."

How can that be? There are string values in the Panel Data.

Skip,"

I stated at that there were no strings and all values were numbers. "How can that be?" well I explained it Steps for you:

1. Format Cells
2. Under Numher select Custom
3. Enter whatever you want ie. "R-"000
4. Whala you have NUMBERS not STRINGS."

You questioned me about doing something that can obviously be done.

"Then maybe you should have explained that you applied a custom format, only we're not actually mind readers in here." I said they were numbers not strings, sorry if any of that confused people.

I tried to explain the situation the best that I could and even showed pics. I hate it when being on forums and 1 person may not no the answer so they try and push you somewhere eles.

"Since you are an Excel expert, I won't bore you with the details of how to apply these in conjunction with your almost-there-PivotTable."

I never stated that I was an exper but creating "R-"000 for the cell format is not an advanced option and they way I was talked to sounded like I was stupid and I have no clue on whats going on, or I made up stuff.

Thats what bugged me, sorry if I offended anyone. I was just trying to look for a conclusion on how to do these in excell without VBA, UNLESS I really have to use it and theres no other way. I don't know a lot of VBA so I would like to stay away from that for this.

Also, I have done something like this in the past, but I can't remeber what I did and I didn't use VBA.

Anyways sorry again and I'll keep working with the pivot tables.


Matt

 

[SkipVought]

BTW when you stated, "...The "R-" is added to the NUMBER ..." it implies CONCATENATION.

What you described later is a DISPLAY FORMAT which ADDS NOTHING to the value.

Clear, concise and complete requirements go a long way toward getting good results.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[Davidprince]

MFleming
Whilst I have looked at your website (currently under construction) and your link which, because it is just a picture, doesn't explain the nature of the problem, I think that a couple of concrete examples may help. For instance, you have a panel number, a reinforcing number in separate columns (as in your .gif) and in column C SQFT which I assume is square feet. In addition, you have columns E - K which has headings REINF.# (E) and PANEL# (F - K).
In the image, all of column B appears the same (that being 2) as is all of column C (being 107), so what is the relationship between numbers in column A and B and the table (REINF.# (E) and PANEL# (F - K)) or is the table a Lookup table?
For instance for R-07, Panels 025, 027, 062 or 134 could be used. Clearly, these panel numbers come from a list of some sort, either a book, or manuals the company uses.
Some detailed examples would be useful and would go a long way to solving your problem.
Reagrds
David

 

[mfleming]

Background Info:

We use AutoCad to draw our precast panels. We have an ID Block which has attributes.

Attributes:
Panel#
Sqft
Reinf#

I extract all the attributes from all the panels in the drawings into an excel sheet.

When I first open the excel sheet after the export out of excel the values are ie.

Panel = 1, 2, 35, ect.
Sq.ft doesn't matter for this example it is used somewhere else.
Reinf# = 1, 2, 45, 33, ect.

I then add "R-"00 to each of the reinf numbers. so they become R-01, R-10, R-05 ect. they all retain their format as being numbers.

I then change the format for panel to 000 so they will display as: 001, 005, 006, 010 ect.

What My End Result is:
- I put all the data in rows and columns as shown

http://www.homenkobuilders.com/reinf.gif

on the right hand side.

Next I copy and past the those highlighted columns and paste them into notepad.

Next when I'm AutoCAD I use import the next to be displayed as I have shown it. Here is what it looks like.

http://www.homenkobuilders.com/reinf3.gif

The Headers ie Panel# Reinf# ect. don't mean or do anything. I delete them after I export to text doc and re-input them in AutoCAD.


Basicaly I want to arange the data I get from autocad into a particular layout I have shown in my examples. When they go back into Autocad it doesn't matter if the are numbers, variables, strings ect. The look and corresponding panels with each reinforcing has to be correct.


I hope this helps more.

Matt

 

[SkipVought]

Here's a totally numeric approch using a PivotTable.

1) add 2 helper columns -- headings Col & RW

2) use Insert/Name/Create - create names in top row

3) Named Range NumCols contains the number of columns you want in your pivottable.

4) Col Formula in C2
[tt]
=IF(A2<>A1,1,IF(C1=NumCols,1,C1+1))
[/tt]
5) RW Formula in D2
[tt]
=IF(C2=1,IF(ROW()=2,0,D1)+1,D1)
[/tt]
6) Pivot Table
Row AREA
[tt]
REINF
RW
[/tt]
COLUMN AREA
[tt]
Col
[/tt]
DATA AREA
[tt]
Max of Panel
[/tt]
This still does not come near to automating the process. There seems to be some essential information missing in your requirement statement.


Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[SkipVought]

I have my table set up starting in COLUMN F.

REINF: column F, Row 2
[tt]
=INDEX(REINF,MATCH(ROW()-1,RW,0),1)
[/tt]
Panel: column G, Row 2
[tt]
Copy down

=INDEX(OFFSET($A$1,MATCH($F2,REINF,0),1,COUNTIF(REINF,$F2),1),MATCH(COLUMN()-6,Col,0)+(ROW()-1-INDEX(RW,MATCH($F2,REINF,0),1))*NumCols,1)
[/tt]
Copy over & down.
Use Conditional Formatting to "hide" the #REF! values.

BTW, your SOURCE DATA in columns A & B can be R-01, R-03A, LNK1 or whatever it should be.

VOILA!

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[mfleming]

I can't quite get it all working. Can u post a pic of the sheet with the formulas showing.

 

[SkipVought]

Nope.

What seems to be the problem?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[mfleming]

I have my table set up starting in COLUMN F.

REINF: column F, Row 2

=INDEX(REINF,MATCH(ROW()-1,RW,0),1)

Panel: column G, Row 2

Copy down

=INDEX(OFFSET($A$1,MATCH($F2,REINF,0),1,COUNTIF(REINF,$F2),1),MATCH(COLUMN()-6,Col,0)+(ROW()-1-INDEX(RW,MATCH($F2,REINF,0),1))*NumCols,1)

Copy over & down.
Use Conditional Formatting to "hide" the #REF! values.

BTW, your SOURCE DATA in columns A & B can be R-01, R-03A, LNK1 or whatever it should be.

VOILA!

Skip,"

I folled this but I must be missing something. I havn't used indexing for a long time.

What are all the indexs?

- You have "REINF" which I assume is all values in Reinf col.
- Next it looks like "RW" which is? (panel values?)
- Next is "Col" (reinf values?

 

[SkipVought]

I had deleted my workbook containing the solutions I posted over the past few days.

But I copied each of the expressions, INCLUDING the expressions for Col and RW. I Named all my ranges, including NumCols

Reconstructed the whole thing in about 3 minutes. Worked perfectly.

Explain what is/is not happening and where. Please be specific.

Is COL F working?

Is COL G working?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[SkipVought]

BTW,

Col & RW are the Column and Row values for Panel values in the result range.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
POULTRY in motion to PULLET for a PALTRY amount!}

 

[mfleming]

http://www.homenkobuilders.com/reinf4.gif

Is the "REINF" index the all values or just values in the reinf col ie. A2:A:99..?