Pulling data from table

M

MightyQuinn

Board Regular
Joined
Dec 28, 2007
Messages
152
Hello experts,

Im trying to pull certain data from this "data table" (example below) and have certain parts import into a report (separate excel sheet). I tried several times with different combinations of formulas and I dont think its possible unless it uses complex combinations of formulas which I dont want to use. Also the data table may update from time to time but the overall layout should stay the same. I think the best way is VBA.

-For example if given "ZIM0001" I will need to pull out almost all of the corresponding information (data next to the cells labeled "ZIM0001") into certain cells in the report. For example, when given "ZIM0002" in one cell I may need to pull in "Test Location 1" and "Mid Thickness" and "Elongation test 1" and "18.000" and "35.000" all into certain cells in the report.

With VBA this shouldnt be too hard but it would need to be easy enough for me to work with and change cells around in (which I could easily do).

(I can email this example data table if needed, I couldnt get the HTML maker)
HTML: 
ZIM0001	Impact 100 Structural/Bar	IM100	CH1_SMP1	Charpy - test 1 sample 1	330		20.000000000	999.000000000
ZIM0001	Impact 100 Structural/Bar	IM100	CH1_SMP2	Charpy - test 1 sample 2	340		20.000000000	999.000000000
ZIM0001	Impact 100 Structural/Bar	IM100	CH1_SMP3	Charpy - test 1 sample 3	350		13.300000000	999.000000000
ZIM0001	Impact 100 Structural/Bar	IM100	CH1_AVG	Average Charpy test 1	360		20.000000000	999.000000000
ZIM0001	Impact 100 Structural/Bar	IM100	CH1_TEMP	Charpy Test Temp. test 1	370		-40.000000000	-40.000000000
ZIM0002	Impact 100 No CVN	IM100N	TENSLOC1	Test Location 1	210	Mid Thickness	0.000000000	0.000000000
ZIM0002	Impact 100 No CVN	IM100N	TENORIN1	Test Orientation	220	Longitudinal	0.000000000	0.000000000
ZIM0002	Impact 100 No CVN	IM100N	YIELD_1	Yield Strength test 1	230		100.000000000	999.000000000
ZIM0002	Impact 100 No CVN	IM100N	TENSIL_1	Tensile Strength test 1	240		110.000000000	135.000000000
ZIM0002	Impact 100 No CVN	IM100N	ELONG_1	Elongation test 1	250		18.000000000	35.000000000
ZIM0002	Impact 100 No CVN	IM100N	ELGAGE_1	Elongation Gage Lgth test 1	260		2.000000000	8.000000000
ZIM0002	Impact 100 No CVN	IM100N	REDAREA1	Reduction of Area test 1	270		40.000000000	999.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	TENSLOC1	Test Location 1	210	Mid Thickness	0.000000000	0.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	TENORIN1	Test Orientation	220	Longitudinal	0.000000000	0.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	YIELD_1	Yield Strength test 1	230		100.000000000	999.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	TENSIL_1	Tensile Strength test 1	240		110.000000000	135.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	ELONG_1	Elongation test 1	250		18.000000000	35.000000000
ZIM0003	Impact 100 -50F CVN	IM100M	ELGAGE_1	Elongation Gage Lgth test 1	260		2.000000000	8.000000000

Any help greatly appreciated,
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
Sort by date Sort by votes
MightyQuinn,

If the following text string was in cell A1:

ZIM0001 Impact 100 Structural/Bar IM100 CH1_SMP1 Charpy - test 1 sample 1 330 20.000000000 999.000000000


If this data was split, what would be in the corresponding columns/cells to the right:

B1 ZIM0001
C1
D1
...
 
Upvote 0
Thanks for your responce. The data is split into different cells so it would be:

A1 ZIM0001 B1 Impact 100 Structural/Bar C1 IM100 D1 CH1_SMP1 E1 Charpy - test 1 sample 1 F1 330 G1 "blank" H1 20.0000 J1 999.000

A2 ZIM0001 B2 Impact 100 Structural/Bar C2 IM100 D2 CH1_SMP2 E2 Charpy - test 1 sample 2 ...

Sorry that tag i sent was not very good, let me know if you want more of it or just the actual excel table.

Thanks again
 
Upvote 0
MightyQuinn,

example data table
Click to expand...


In reference to this table:

See my Private Message to you (top right hand corner of MrExcel, Welcome, MightyQuinn., Private Messages:
 
Upvote 0
MightyQuinn,

Thanks for the workbook and the template. I can not use these files.

Please send the raw data file that is in your first post.
 
Upvote 0
MightyQuinn,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).


1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.


Code: 
Option Explicit
Sub FormatResults()
' hiker95, 03/10/2011
' http://www.mrexcel.com/forum/showthread.php?t=534841
With Application
  .DisplayAlerts = False
  .ScreenUpdating = False
  With ActiveSheet
    .Rows(1).Insert
    With .Range("A1:I1")
      .Value = [{"Group","Task list description","Old Spec Code","MstrChar","Short text for the inspection charac.","Char","Location/Orientation","Lower tol.limit","Upper specLimit"}]
      .Interior.ColorIndex = 15
    End With
    .Columns(3).Replace What:=" ", Replacement:="", LookAt:=xlPart, _
      SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
    .Range("H1:I" & .Cells(Rows.Count, 1).End(xlUp).Row).NumberFormat = "0.000000000"
    With .UsedRange
      .Columns.AutoFit
      .AutoFilter
    End With
    .Range("A1").Select
  End With
  .DisplayAlerts = True
  .ScreenUpdating = True
End With
End Sub



Sampe empty worksheet to contain the text file data:


Excel Workbook
ABCDEFGHIJ
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Sheet1





Part of the following may be able to be automated:

1. Open the text file.

2. Highlite all the data, then copy (CTRL + C)

3. In an empty worksheet, click in cell A1

4. Click and hold down the ALT key, then click on the E key

5. Click on the S key

6. In the As: box

7. Click on Text, and then click on the OK button.




And you will get this:


Excel Workbook
ABCDEFGHIJ
1ZIM0001Impact 100 Structural/BarIM100CH1_SMP1Charpy - test 1 sample 133020999
2ZIM0001Impact 100 Structural/BarIM100CH1_SMP2Charpy - test 1 sample 234020999
3ZIM0001Impact 100 Structural/BarIM100CH1_SMP3Charpy - test 1 sample 335013.3999
4ZIM0001Impact 100 Structural/BarIM100CH1_AVGAverage Charpy test 136020999
5ZIM0001Impact 100 Structural/BarIM100CH1_TEMPCharpy Test Temp. test 1370-40-40
6ZIM0002Impact 100 No CVN IM100NTENSLOC1Test Location 1210Mid Thickness00
7ZIM0002Impact 100 No CVN IM100NTENORIN1Test Orientation220Longitudinal00
8ZIM0002Impact 100 No CVN IM100NYIELD_1Yield Strength test 1230100999
9ZIM0002Impact 100 No CVN IM100NTENSIL_1Tensile Strength test 1240110135
10ZIM0002Impact 100 No CVN IM100NELONG_1Elongation test 12501835
11ZIM0002Impact 100 No CVN IM100NELGAGE_1Elongation Gage Lgth test 126028
12ZIM0002Impact 100 No CVN IM100NREDAREA1Reduction of Area test 127040999
13ZIM0003Impact 100 -50F CVN IM100MTENSLOC1Test Location 1210Mid Thickness00
14ZIM0003Impact 100 -50F CVN IM100MTENORIN1Test Orientation220Longitudinal00
15ZIM0003Impact 100 -50F CVN IM100MYIELD_1Yield Strength test 1230100999
16ZIM0003Impact 100 -50F CVN IM100MTENSIL_1Tensile Strength test 1240110135
17ZIM0003Impact 100 -50F CVN IM100MELONG_1Elongation test 12501835
18ZIM0003Impact 100 -50F CVN IM100MELGAGE_1Elongation Gage Lgth test 126028
19
20
Sheet1





Then run the FormatResults macro.



And you will get this:


Excel Workbook
ABCDEFGHIJ
1GroupTask list descriptionOldSpecCodeMstrCharShort text for the inspection charac.CharLocation/OrientationLower tol.limitUpper specLimit
2ZIM0001Impact 100 Structural/BarIM100CH1_SMP1Charpy - test 1 sample 133020.000000000999.000000000
3ZIM0001Impact 100 Structural/BarIM100CH1_SMP2Charpy - test 1 sample 234020.000000000999.000000000
4ZIM0001Impact 100 Structural/BarIM100CH1_SMP3Charpy - test 1 sample 335013.300000000999.000000000
5ZIM0001Impact 100 Structural/BarIM100CH1_AVGAverage Charpy test 136020.000000000999.000000000
6ZIM0001Impact 100 Structural/BarIM100CH1_TEMPCharpy Test Temp. test 1370-40.000000000-40.000000000
7ZIM0002Impact 100 No CVNIM100NTENSLOC1Test Location 1210Mid Thickness0.0000000000.000000000
8ZIM0002Impact 100 No CVNIM100NTENORIN1Test Orientation220Longitudinal0.0000000000.000000000
9ZIM0002Impact 100 No CVNIM100NYIELD_1Yield Strength test 1230100.000000000999.000000000
10ZIM0002Impact 100 No CVNIM100NTENSIL_1Tensile Strength test 1240110.000000000135.000000000
11ZIM0002Impact 100 No CVNIM100NELONG_1Elongation test 125018.00000000035.000000000
12ZIM0002Impact 100 No CVNIM100NELGAGE_1Elongation Gage Lgth test 12602.0000000008.000000000
13ZIM0002Impact 100 No CVNIM100NREDAREA1Reduction of Area test 127040.000000000999.000000000
14ZIM0003Impact 100 -50F CVNIM100MTENSLOC1Test Location 1210Mid Thickness0.0000000000.000000000
15ZIM0003Impact 100 -50F CVNIM100MTENORIN1Test Orientation220Longitudinal0.0000000000.000000000
16ZIM0003Impact 100 -50F CVNIM100MYIELD_1Yield Strength test 1230100.000000000999.000000000
17ZIM0003Impact 100 -50F CVNIM100MTENSIL_1Tensile Strength test 1240110.000000000135.000000000
18ZIM0003Impact 100 -50F CVNIM100MELONG_1Elongation test 125018.00000000035.000000000
19ZIM0003Impact 100 -50F CVNIM100MELGAGE_1Elongation Gage Lgth test 12602.0000000008.000000000
20
Sheet1
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,224,503
Messages
6,179,136
Members
452,890
Latest member
Nikhil Ramesh

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top