creating data table

[22strider]

Hello Friends

I need to create a table after applying some logic to another data table. What is the best way to go about? I am not good with arrays but have feeling that I may have to go that way. if someone can guide then I'll try to find my way through.

I wanted to post full detail of what I need to do but it was getting little hard to explain.

Thanks
Rajesh

 

[hiker95]

22strider,

What version of Excel are you using?

Can you post the raw data worksheet, and, post the worksheet results (manually formatted by you) that you are looking for?

To post your data, you can download and install one of the following two programs:
Excel Jeanie
MrExcel HTML Maker

Or, when using Internet Explorer, just put borders around your data in Excel and copy those cells into your post.


If you are not able to give us screenshots:
You can upload your workbook to Box Net,

sensitive data scrubbed/removed/changed

mark the workbook for sharing
and provide us with a link to your workbook.

 

[22strider]

Hello Hiker95

Thanks for your message. I had posted a detailed request earlier but didn't get many responses. Here is the link to that I hope it will give you an idea what I am trying to do

http://www.mrexcel.com/forum/excel-questions/660529-data-re-arranging-index-file.html

Thanks
Rajesh

 

[hiker95]

22strider,

I looked at your other post.

I can not tell what cells, rows, columns, your raw data is in.

And, I can not tell what cells, rows, columns, your result(s) are in.


If you can not give us screenshots (per my last reply), or, you can not post your workbook (per my last reply), then:

Click on the Reply to Thread button, and just put the word BUMP in the post. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[22strider]

Hello Hiker95

I've posted tables on my original thread. I am posting them here as well. Thanks for pointing me towards the link for html maker. I've always struggled with tables not getting posted corectly.

Excel 2007
	B	C	D	E	F
2			Table1
3	Parent Part	Child Part	Qty	Ref Desig	New Child Part
4	150-0000	500-681AB	1	C3	500-681AB
5	150-0000	500-103AB	1	C4	500-103AB
6	150-0000	500-222AB	1	C2	500-222
7	150-0000	500-154AB	1	R3	500-154
8	150-0000	500-155	1	R2	500-155LF
9
10			Table2
11	Parent Part	Child Part	Qty	Ref Desig	IsDelete
12	150-0000	500-681AB	1	C3	No
13	150-0000	500-103AB	1	C4	No
14	150-0000	500-222AB	1	C2	Yes
15	150-0000	500-154AB	1	R3	Yes
16	150-0000	500-155	1	R2	Yes
17	150-0000	500-222	1	C2	No
18	150-0000	500-154	1	R3	No
19	150-0000	500-155LF	1	R2	No
Sheet1

 

[hiker95]

22strider,

Can we have a screenshot (manually formatted by you) of what the results should look like?

Will the results be on the same/different worksheet?

 

[hiker95]

22strider,

1. Are Table1 and Table2 range names?

2. Does your Table1 reside in worksheet Sheet1, with the title Parent Part in cell B2?

3. Does Table2 already exist? If YES, what cell is the title Parent Part in?

 

[22strider]

Hello Hiker95

1) I named Table 1 and 2 just to refer them in my request for help. They are not range names. Table1 just shows hoe the original data is formatted. Table2 shows how the result data need to appear.
2) The original data appears in one spreadsheet, the result data needs to be in another spreadsheet. Starting from any cell.
3) Table2 does not exist.

Thanks for your help
Rajesh

 

[22strider]

Hello Hiker95

1) I named Table 1 and 2 just to refer them in my request for help. They are not range names. Table1 just shows hoe the original data is formatted. Table2 shows how the result data need to appear.
2) The original data appears in one spreadsheet, the result data needs to be in another spreadsheet. Starting from any cell.
3) Table2 does not exist.

Thanks for your help
Rajesh

 

[hiker95]

22strider,


Sample raw data in worksheet Sheet1:

Excel Workbook
	A	B	C	D	E
1	Parent Part	Child Part	Qty	Ref Desig	New Child Part
2	150-0000	500-681AB	1	C3	500-681AB
3	150-0000	500-103AB	1	C4	500-103AB
4	150-0000	500-222AB	1	C2	500-222
5	150-0000	500-154AB	1	R3	500-154
6	150-0000	500-155	1	R2	500-155LF
7
Sheet1

After the macro in a new worksheet Results:

Excel Workbook
	A	B	C	D	E
1	Parent Part	Child Part	Qty	Ref Desig	IsDelete
2	150-0000	500-681AB	1	C3	No
3	150-0000	500-103AB	1	C4	No
4	150-0000	500-222AB	1	C2	Yes
5	150-0000	500-222	1	C2	No
6	150-0000	500-154AB	1	R3	Yes
7	150-0000	500-154	1	R3	No
8	150-0000	500-155	1	R2	Yes
9	150-0000	500-155LF	1	R2	No
10
Results

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.

Option Explicit
Sub CreateTable()
' hiker95, 09/23/2012
' http://www.mrexcel.com/forum/excel-questions/660737-creating-data-table.html
Dim w1 As Worksheet, wR As Worksheet
Dim r As Long, lr As Long, nr As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
If Not Evaluate("ISREF(Results!A1)") Then Worksheets.Add(After:=w1).Name = "Results"
Set wR = Worksheets("Results")
wR.UsedRange.Clear
wR.Cells(1, 1).Resize(, 4).Value = w1.Cells(1, 1).Resize(, 4).Value
wR.Cells(1, 5) = "IsDelete"
lr = w1.Cells(Rows.Count, 1).End(xlUp).Row
For r = 2 To lr Step 1
  If w1.Cells(r, 2).Value = w1.Cells(r, 5).Value Then
    nr = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    wR.Cells(nr, 1).Resize(, 4).Value = w1.Cells(r, 1).Resize(, 4).Value
    wR.Cells(nr, 5) = "No"
  Else
    nr = wR.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    wR.Cells(nr, 1).Resize(, 4).Value = w1.Cells(r, 1).Resize(, 4).Value
    wR.Cells(nr, 5) = "Yes"
    nr = nr + 1
    wR.Cells(nr, 1).Value = w1.Cells(r, 1).Value
    wR.Cells(nr, 2).Value = w1.Cells(r, 5).Value
    wR.Cells(nr, 3).Resize(, 2).Value = w1.Cells(r, 3).Resize(, 2).Value
    wR.Cells(nr, 5) = "No"
  End If
Next r
wR.UsedRange.Columns.AutoFit
wR.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm


Then run the CreateTable macro.