extract data of a list from a list.

[Anusuya12]

I tried recording a macro to extract data of a list from a list.
But I could not succeed in it.
Can anyone help me out on this. I dont know how to attach my sample data here.

In Data sheet column A has some data like date,-1, and some more numbers
an Column B has numbers.
In Column AA there is a list of numbers.
Coding required is to extract the list of numbers in column AA
from cloumn A of the DATA sheet along with the number in column B
after 6 cells.
Example: Column AA39 has 5938791. The coding should identify this number from Column A (A33) along with the number in B39.
Note that all the numbers in column B wil be in this cell difference
of 6.
And extracted the list should come in Final Sheet.
Your assistance is much appreciated.

 

[hiker95]

Anusuya12,

Welcome to the MrExcel forum.


What version of Excel are you using?

You will generally get much more help (and faster) in this forum if you can post your small samples directly in the forum.

Please attach screenshots of your workbook or a sample workbook that accurately portrays your current workbook on one sheet, and what it should look like 'After' on another sheet.

This makes it much easier to see exactly what you want to do, as well as shows us whether there is a consistent number of rows between tables and such.

Here are three possible ways to post small (copyable) screen shots directly in your post:

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

or
RichardSchollar’s beta HTML Maker -...his signature block at the bottom of his post

or
Borders-Copy-Paste



If you are not able to give us screenshots:

To get the most precise answer, it is best to upload/attach a sample workbook (sensitive data scrubbed/removed) that contains an example of your raw data on one worksheet, and on another worksheet your desired results.

The structure and data types of the sample workbook must exactly duplicate the real workbook. Include a clear and explicit explanation of your requirements.

You can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[Anusuya12]

Thanks for your response.
http://www.box.net/shared/qz04urpagk
I have saved the sample data in the above link.
Details:
There is a list of numbers in column AA.
In Data Sheet Column A there are some data's which includes some of the numbers
in column AA.
In Data Sheet Column B there are some numbers again.
I need a coding to check if any of the number of column AA exists
in Column A, If yes then it should be copied to the next sheet along with
the number in B column after 6 cells.
I have highlighted one example in yellow,
If the number is in A33 then the corresponding number will be in
B39.
Now I need this A33 and B39 values to be copied and pasted in final
sheet.
Thanks.

 

[hiker95]

Anusuya12,


Sample raw data in worksheet Data:

Excel Workbook
	A	B	AA
2			3861
3			23331
4			5711
5			146981
6			420921
7			503911
8			529871
9			577021
10			577101
11			577291
12			596871
13	2/7/2011		597591
14	-1		630451
15			630531
16			205131
17			224711
18	2/7/2011		379051
19	-1		121441
20			144731
21			161611
22			503271
23	2/7/2011		621221
24	-1		658811
25			775741
26			786651
27			825681
28	2/7/2011		825761
29	-1		825841
30			489331
31			993321
32			1036701
33	9387911		1036881
34			1036961
35			1037121
36			1037611
37			1335801
38			1494871
39		4	9387911
Data

After the macro in worksheet Final:

Excel Workbook
	A	B
1
2	529871	9
3	577291	1
4	597591	3
5	121441	2
6	144731	3
7	775741	2
8	786651	5
9	825681	4
10	825761	1
11	489331	3
12	993321	3
13	9387911	4
14	75344881	1
15	51448091	5
16	838632391	6
17	9001182451
18	9001182611
19	9003369001	3
20
Final

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 GetData()
' hiker95, 02/27/2011
' http://www.mrexcel.com/forum/showthread.php?t=532225
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Data")
Set w2 = Worksheets("Final")
For Each c In w1.Range("AA2", w1.Range("AA" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w1.Columns(1), 0)
  On Error Resume Next
  If FR > 0 Then
    NR = w2.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    w2.Cells(NR, 1) = c.Value
    w2.Cells(NR, 2).Value = w1.Cells(FR + 6, 2).Value
  End If
Next c
w2.Columns("A:B").AutoFit
w2.Activate
Application.ScreenUpdating = True
End Sub

Then run the GetData macro.

 

[Anusuya12]

Its working great thanks...

Have a wonderful day ahead...

 

[Anusuya12]

Hi,

The data which I sent u was extracted after running the below code in an excel. This is a recorded macro.

And the coding you gave is after this step. Can you please let me know how to combine both the coding so that I have a single macro.

Sorry I should have given this information before hand.


Sub Report()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _<o></o>
FieldInfo:=Array(Array(0, 1), Array(14, 1), Array(38, 1), Array(53, 1), Array(57, 1), _<o></o>
Array(60, 1), Array(64, 1), Array(73, 1), Array(88, 1), Array(105, 1), Array(127, 1), Array _<o></o>
(132, 1)), TrailingMinusNumbers:=True<o></o>
Cells.Select<o></o>
Cells.EntireColumn.AutoFit<o></o>
Columns("B:K").Select<o></o>
Selection.Delete Shift:=xlToLeft<o></o>
Columns("A:A").Select<o></o>
Selection.SpecialCells(xlCellTypeConstants, 2).Select<o></o>
Selection.EntireRow.Delete<o></o>
End Sub<o></o>

 

[hiker95]

Anusuya12,


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.

Sub ReportV2()
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _
  FieldInfo:=Array(Array(0, 1), Array(14, 1), Array(38, 1), Array(53, 1), Array(57, 1), _
  Array(60, 1), Array(64, 1), Array(73, 1), Array(88, 1), Array(105, 1), Array(127, 1), Array _
  (132, 1)), TrailingMinusNumbers:=True
Cells.Select
Cells.EntireColumn.AutoFit
Columns("B:K").Select
Selection.Delete Shift:=xlToLeft
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeConstants, 2).Select
Selection.EntireRow.Delete
''Sub GetData()
'' hiker95, 02/27/2011
'' http://www.mrexcel.com/forum/showthread.php?t=532225
Dim w1 As Worksheet, w2 As Worksheet
Dim c As Range, FR As Long, NR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Data")
Set w2 = Worksheets("Final")
For Each c In w1.Range("AA2", w1.Range("AA" & Rows.Count).End(xlUp))
  FR = 0
  On Error Resume Next
  FR = Application.Match(c, w1.Columns(1), 0)
  On Error Resume Next
  If FR > 0 Then
    NR = w2.Range("A" & Rows.Count).End(xlUp).Offset(1).Row
    w2.Cells(NR, 1) = c.Value
    w2.Cells(NR, 2).Value = w1.Cells(FR + 6, 2).Value
  End If
Next c
w2.Columns("A:B").AutoFit
w2.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReportV2 macro.

 

[Anusuya12]

Hi Hiker,
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
I tried the above coding and it works for only for some of the reports. I found why it is not pulling the data correctly. It is because the difference of cells for the some of the data is not 6, it exceeds 6. And again there are subtotals in between. Hence I have decided to give you the dummy data of the whole sheet.
<o> </o>
Below is the link where I have saved the new sample.

http://www.box.net/shared/otq6hukphh
<o> </o>
I have pasted a report to column A of Data Worksheet.
<o> </o>
From this data I need the coding to identify only the account number listed in Final Worksheet and paste the total items number against the correct account number in cell highlighted in pink.

I have highlighted the account numbers and their total items number in blue color font for easy reference.
<o> </o>
And if the account number in Final sheet list is not there in Data sheet, then it should display as 0.
<o> </o>
Note that the report has four subdivisions in the sample attached – FORG, JIKAJIKIJUKKU, KADAMB and UTDFT for which the total is given separately. I have highlighted those totals in Lime.
Also it has grand total to it.
<o> </o>
<o> </o>
The respective total with the heading should be displayed in Data Sheet itself once macro completes.
<o> </o>
<o> </o>
Thanks in advance.

 

[hiker95]

Anusuya12,

I tried the above coding and it works for only for some of the reports. I found why it is not pulling the data correctly. It is because the difference of cells for the some of the data is not 6, it exceeds 6.

What is the max number greater then 6 that the difference could be?

 

[Anusuya12]

It cannot be defined as it differs from report to report. In the sample attached itself we have one account number with the difference of 18 and another with 19.

So we cannot go by line spacing.