Importing AutoCAD data. Need specific data looked up and placed on different tab.

[dollada06]

This might be a bit long, but I want to explain it clearly so you all have the best chance of helping me.

First. The data I am importing is coming from AutoCAD using their data extraction feature and looks like this. We are extracting a size and length for an electrical cable. The GROUP number increases up to 8. That is the maximum groups we will ever have.

We need all this data in this exact format for reasons I will not get into too deeply, but basically, the DC-CAB layer is added by the group as well so it needs to be there.

What I am trying to achieve is to have Excel look through this list of data and then identify all of the rows that are in GROUP 1 and also have a NAME that contains "*DC--HR*". When it finds a match it pastes those rows THICKNESS and LENGTH value to a separate sheet into a specific spot. It would then do the same operation for GROUP 2, GROUP 3 and so on. Does that makes sense?

I am a little better than a novice at Excel so if there is a way to do this without using VBA I would prefer that, but I have set up some VBA buttons before worst case I can do that as well.

Last. Here is a link to an example of what I am trying to do. Hope this can help. Thank you all so much in advance for any help you can give me. I have been struggling with this all day and I am just about ready to give up. I've exhausted my excel ability to figure it out on my own so I humbly ask for your help.

Adam

 

[mrshl9898]

Sounds like a few pivot tables will do this. First one filtered on group1 and dc--hr with thickness and length in the rows section, second with group 2 etc.

 

[dollada06]

Sounds like a few pivot tables will do this. First one filtered on group1 and dc--hr with thickness and length in the rows section, second with group 2 etc.

Unfortunately I am not all that familiar with pivot tables. Is there any chance you are willing to set up a group or two with the example file I created and I can reverse engineer it into our actual excel file? As long as it can make a distinction between the groups and apply them to the correct spot on the other sheet that would be an excellent way to do it. I just dont know how without researching it a bunch.

 

[mrshl9898]

I can't dl your file (work network restrictions), it's only a few steps though.

1. Select all your data including headings.
2. Insert > Pivot Table. A box will pop up, select 'existing location' and select cell A1 on the sheet you need the data sorted in. (or just use the default new worksheet and copy it over later)
3. On the right of screen all your headings will be up top. Drag 'group' and 'name' into the filter section below and 'size' and 'length' into the rows section.
4. You may need to go to design > report layout > show in tabular form. (also perhaps repeat all item labels depending how the data looks and needs to look)

 

[dollada06]

I can't dl your file (work network restrictions), it's only a few steps though.

1. Select all your data including headings.
2. Insert > Pivot Table. A box will pop up, select 'existing location' and select cell A1 on the sheet you need the data sorted in. (or just use the default new worksheet and copy it over later)
3. On the right of screen all your headings will be up top. Drag 'group' and 'name' into the filter section below and 'size' and 'length' into the rows section.
4. You may need to go to design > report layout > show in tabular form. (also perhaps repeat all item labels depending how the data looks and needs to look)

I experimented with this quite a bit last night and figured out how to set up a pivot table, but I am not following how I can get those specific values onto another sheet into specific cells.

 

[dollada06]

OK. I spoke with a supervisor and they are very adamant about not adding another sheet to our file. There are only 8 pages. Don't ask why. I tried. I am being more or less being forced to go the VBA route. So if we can work that way it would still solve my problem. Same approach, but I can have it be a button that runs it.

 

[mrshl9898]

I think this specific order deal is making the pivot not possible (sorry, I did miss that part)

I'll need some info, as I cannot see your imgur file now (looks to be a different work access issue).

What is the sheet name with the raw data?
What is the sheet name with for the output?
Which column contains "*DC--HR*"?
Which column contains the Group number?
What are the columns that contain thickness and length?
What specific cells do they need to go into? Same row numbers as the raw data?

 

[mrshl9898]

I'll assume some answers below, hopefully this helps you amend the code to do exactly what you want.

"Data"
"Output"
A (1)
D (4)
B&C (2&3)
Yes

Sub extractdata()


Dim rownum As Long
Dim rownum2 As Long


rownum = 2
rownum2 = 2

Sheets("Data").Select


Do Until Sheets("Data").Cells(rownum, 1).Value = ""


    If Sheets("Data").Cells(rownum, 1).Value Like "*DC--HR*" Then
    Sheets("Data").Range(Cells(rownum, 2), Cells(rownum, 3)).Copy


        If Sheets("Data").Cells(rownum, 4).Value = "Group 1" Then
        Sheets("Output").Cells(rownum2, 1).PasteSpecial xlPasteValues
        ElseIf Sheets("Data").Cells(rownum, 4).Value = "Group 2" Then
        Sheets("Output").Cells(rownum2, 3).PasteSpecial xlPasteValues
        ElseIf Sheets("Data").Cells(rownum, 4).Value = "Group 3" Then
        Sheets("Output").Cells(rownum2, 5).PasteSpecial xlPasteValues
        ElseIf Sheets("Data").Cells(rownum, 4).Value = "Group 4" Then
        Sheets("Output").Cells(rownum2, 7).PasteSpecial xlPasteValues
        ElseIf Sheets("Data").Cells(rownum, 4).Value = "Group 5" Then
        Sheets("Output").Cells(rownum2, 9).PasteSpecial xlPasteValues
        End If
    
    End If
    
rownum = rownum + 1
rownum2 = rownum2 + 1
Loop


End Sub