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,

In sheet Data, after your macro code, there is no list in column AA to be used to search.

I have worked out the code to search for the numbers in columns B and M for the new structure for worksheet Final.

But, I can not see the way to find the correct value in worksheet Data, column B, if the correct value is not 6 rows down from the found row in column A.

I could loop from 6 rows down to 19 rows down for the first value found in that range, but, this may not yield the correct number.


You may want to click on the Post Reply button, and just enter the word BUMP, then click on the Submit Reply button, and someone else will assist you.

 

[hiker95]

Anusuya12,


Sample raw data before the macro:

Excel Workbook
	A
1
2
3
4	1 APW System Esc. 7.5.10
5
6	Report 124 - Daily Status > 8 Days (On Friday) Run Date: 02/24/11 Page 1
7
8	Posted Date: From 1/1/70 To 02/16/11 Work Date: 02/24/11 Time: 7:10:53AM
9
10	Co. Name: CANJ CDFON Accounts Pool: 0500133850 ANU TEASFJK GHJNEKISNEMT
11
12	Dept: FORG
13
14
15	**** **** ***** **** ***** **** ***** ** *********** **** ****** ******
16	hfshdfh gfsgdfggdfg gfdgfdhdfhg sdgffd sgffgl
17
18	02/10/11 02/10/11 114,978.24 CR BO snjkosedio 175
19	-1 0500133850 01113 175
20
21
22	Message fdadfnjknljkhiundasfuihdsjhdiuhnkjufhnkjhohjnjknlkjoijkljpojlkjhoiup
23	asdfs,mfnkldnskfndkjnfdjfknnhjuhbyuvhggbvhkbhghbgkjyhiuhkjhyuhjhikhkuhjf
24	nl.khjljkjnlik
25
26	0500133850 ANU TEASFJK GHJNEKISNEMT
27
28
29	Credit Debit Net Total Total
30	Amount Items Amount Items Amount Amount Items
31
32	$114,978.24 1 $0.00 0 $-114,978.24 $114,978.24 1
33
Data

Excel Workbook
	A	B	C	D
3
4		JIKAJIKIJUKKU
5		asdf	fasdfa	fads
6		fadsf	fadsfasd	fads
7	1	132145	adsmfldjkf	0
8	2	234	fadsfasd	0
9	3	15648745	fadsfasd	0
10	4	1584851	fsdf	0
11	5	1845132	fdsaf	0
12	6	19644562	vfsdagf	0
13	7	189452189	vasdf	0
14	8	1658	fasdfa	0
15	9	3216894	adsmfldjkf	0
16	10	15628	fadsfasd	0
17	11	12418741	fadsfasd	0
18	12	185964456	fsdf	0
19	13	18745614874	fdsaf	0
20	14	14987465	vfsdagf	0
21	15	4187451	vasdf	0
22	16	487514	fasdfa	0
23	17	487456	adsmfldjkf	0
24	18	1254548	fadsfasd	0
25	19	21578	fadsfasd	0
26	20	20157	fsdf	0
27	21	10578	fdsaf	0
28	22	21878	vfsdagf	0
29	23	25631	vasdf	0
30	24	2187	fasdfa	0
31	25	9800112265	vfsdagf	0
32	26	9800112251	vasdf	0
33	27	2356988944	fasdfa	0
34			TOTALS:	0
35			TOTALS:	186
Final

After the macro:

Excel Workbook
	A	B
1
2
3
4	1
5
6
7
8
9
10
11
12
13
14	2/10/2011
15	-1
16
17
18
19
20
21
22
23
24
25
26		1
27
28
29
30
31
32
33
Data

Excel Workbook
	A	B	C	D
3
4		JIKAJIKIJUKKU
5		asdf	fasdfa	fads
6		fadsf	fadsfasd	fads
7	1	132145	adsmfldjkf	0
8	2	234	fadsfasd	0
9	3	15648745	fadsfasd	0
10	4	1584851	fsdf	0
11	5	1845132	fdsaf	0
12	6	19644562	vfsdagf	0
13	7	189452189	vasdf	0
14	8	1658	fasdfa	0
15	9	3216894	adsmfldjkf	0
16	10	15628	fadsfasd	0
17	11	12418741	fadsfasd	0
18	12	185964456	fsdf	0
19	13	18745614874	fdsaf	0
20	14	14987465	vfsdagf	0
21	15	4187451	vasdf	0
22	16	487514	fasdfa	0
23	17	487456	adsmfldjkf	0
24	18	1254548	fadsfasd	0
25	19	21578	fadsfasd	0
26	20	20157	fsdf	0
27	21	10578	fdsaf	0
28	22	21878	vfsdagf	0
29	23	25631	vasdf	0
30	24	2187	fasdfa	0
31	25	9800112265	vfsdagf	7
32	26	9800112251	vasdf	6
33	27	2356988944	fasdfa	2
34			TOTALS:	15
35			TOTALS:	186
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 ReportV3()
' hiker95, 03/01/2011
' http://www.mrexcel.com/forum/showthread.php?t=532225
Dim w1 As Worksheet, w2 As Worksheet
Dim AArea As Range, SR As Long, ER As Long
Dim c As Range, FR As Long, a As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Data")
Set w2 = Worksheets("Final")
w1.Columns(1).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
w1.UsedRange.Columns.AutoFit
w1.Columns("B:K").Delete
w1.Columns(1).SpecialCells(xlCellTypeConstants, 2).EntireRow.Delete
For Each AArea In w2.Range("A1", w2.Range("A" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With AArea
    SR = .Row
    ER = SR + .Rows.Count - 1
    For Each c In w2.Range("B" & SR & ":B" & ER)
      FR = 0
      On Error Resume Next
      FR = Application.Match(c, w1.Columns(1), 0)
      On Error Resume Next
      If FR > 0 Then
        
        '*****c.Offset(, 2).Value = w1.Cells(FR + 6, 2).Value
        
        For a = FR + 6 To FR + 19 Step 1                  '***** 6 thru 19
          If w1.Cells(a, 2) <> "" Then
            c.Offset(, 2).Value = w1.Cells(a, 2).Value
            Exit For
          End If
        Next a
        
        If c.Offset(, 2).Value = "" Then c.Offset(, 2).Value = 0
      End If
    Next c
  End With
Next AArea

For Each AArea In w2.Range("K1", w2.Range("K" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants).Areas
  With AArea
    SR = .Row
    ER = SR + .Rows.Count - 1
    For Each c In w2.Range("L" & SR & ":L" & ER)
      FR = 0
      On Error Resume Next
      FR = Application.Match(c, w1.Columns(1), 0)
      On Error Resume Next
      If FR > 0 Then
        
        '*****c.Offset(, 2).Value = w1.Cells(FR + 6, 2).Value

        For a = FR + 6 To FR + 19 Step 1                  '***** 6 thru 19
          If w1.Cells(a, 2) <> "" Then
            c.Offset(, 2).Value = w1.Cells(a, 2).Value
            Exit For
          End If
        Next a

        If c.Offset(, 2).Value = "" Then c.Offset(, 2).Value = 0
      End If
    Next c
  End With
Next AArea
w2.Activate
Application.ScreenUpdating = True
End Sub

Then run the ReportV3 macro.

 

[Anusuya12]

Now this is working.
I have tested this macro with 10 reports and its pulling the data's correctly.

Thanks.

 

[hiker95]

Anusuya12,

You are very welcome.

Glad I could help.

Come back any time.

 

[Anusuya12]

Conditionally Insert a row

Hey Hiker,

How are you??

I need your assistance. Am looking for macro coding. See below for details.

I have attached the sample data in the below link.

http://www.box.net/shared/2j2gnpayn2

In Sheet 1 there are two columns as SUB and SHADOW.
In Sheet 2 there are a set of datas, out of which I have colored
three of the numbers in Data 1 column. You can see this numbers in
Sheet 1 under SUB column as well (the colored cell).

Macro should see all the numbers in Data 1 column of Sheet 2 and if
the number exists in SUB column of Sheet 1 then it should insert a
line and the data 1 column should be filled with the corresponding
SHADOW number and rest of the column should be the duplicate of the
above entry.

I have attached a sample for the expected result of the macro in
Sheet 2 under the original data.

 

[hiker95]

Anusuya12,


Sample raw data:

Excel Workbook
	A	B
1	SUB	SHADOW
2	60079	60329
3	60027
4	60028
5	60029
6	60030
7	60399
8	60702	60077
9	60703
10	60704
11	60705
12	60110	60109
13	60112
14	60113
15	60795
16	60799
17	60780	60122
18	60781
19	60782
20	60294	60087
21	60295
22	60296
23	60334
24	60339	60343
25	60340
26	60240
27	60241
28	60802
29	63111	60801
30	63112
31	63113
32	63114
33	60800
34
Sheet1

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6	Data 7	Data 8	Data 9	Data 10
2	50426		int	4/16/2011	4/16/2011			26.23
3	60532		int	4/16/2011	4/17/2011			125.56
4	59621		div	4/16/2011	4/18/2011			245.59
5	60703		div	4/16/2011	4/18/2011			245.59
6	15326		div	4/16/2011	4/19/2011			125.5
7	20546		int	4/16/2011	4/20/2011			362.4
8	60780		int	4/16/2011	4/20/2011			362.4
9	60339		int	4/16/2011	4/20/2011			362.4
10
11
12
13
Sheet2

After the macro on worksheet Sheet2:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Data 1	Data 2	Data 3	Data 4	Data 5	Data 6	Data 7	Data 8	Data 9	Data 10
2	50426		int	4/16/2011	4/16/2011			26.23
3	60532		int	4/16/2011	4/17/2011			125.56
4	59621		div	4/16/2011	4/18/2011			245.59
5	60703		div	4/16/2011	4/18/2011			245.59
6	60077		div	4/16/2011	4/18/2011			245.59
7	15326		div	4/16/2011	4/19/2011			125.5
8	20546		int	4/16/2011	4/20/2011			362.4
9	60780		int	4/16/2011	4/20/2011			362.4
10	60122		int	4/16/2011	4/20/2011			362.4
11	60339		int	4/16/2011	4/20/2011			362.4
12	60343		int	4/16/2011	4/20/2011			362.4
13
Sheet2

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 CopySHADOW()
' hiker95, 04/16/2011
' http://www.mrexcel.com/forum/showthread.php?t=532225&page=2
Dim w1 As Worksheet, w2 As Worksheet
Dim LR As Long, a As Long, aa As Long, FR As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("Sheet1")
Set w2 = Worksheets("Sheet2")
LR = w2.Cells(Rows.Count, 1).End(xlUp).Row
For a = LR To 2 Step -1
  FR = 0
  On Error Resume Next
  FR = Application.Match(w2.Cells(a, 1), w1.Columns(1), 0)
  On Error GoTo 0
  If FR <> 0 Then
    w2.Rows(a + 1).Insert
    w2.Rows(a).Copy w2.Rows(a + 1)
    For aa = FR To 2 Step -1
      If w1.Cells(aa, 1).Offset(, 1) <> "" Then
        w2.Cells(a + 1, 1).Value = w1.Cells(aa, 1).Offset(, 1).Value
        Exit For
      End If
    Next aa
  End If
Next a
w2.Activate
Application.ScreenUpdating = True
End Sub

Then run the CopySHADOW macro.

 

[Anusuya12]

Thanks Hiker... That works great.. I have little changes to match my original data... Is there a place where I could learn how to write coding I just know to record it... Am now learning by browsing some sites.

 

[Anusuya12]

Need your Assistance for one more macro

Need your Assistance for one more macro Hiker... Hope am not troubling you....
When I was browsing sites I learnt how to convert excel data to a template in word.

Below is the link where I have saved my Excel file with the macro.

http://www.box.net/shared/0qmvopqoufhttp://www.box.net/shared/ocppqax5t4

When you run FINAL macro it saves word document with the template (with the data in Formated Sheet).

As Specified in the macro the word document saves in the name as "File and the sequential number".

But I need the documents to be named with the data in Column H of the Formatted Data Sheet sequentially. Also the data in the word document are not formatted properly.

The word document looks like this after the macro.

http://www.box.net/shared/a2bpvdhesu

But I need the Word to show my data in the format below.

http://www.box.net/shared/8hblc2k9qo


Is there a possibility to do this?

Sorry again to bother you.

Your help is much appreciated

Anusuya12.

 

[hiker95]

Anusuya12,

Is there a place where I could learn how to write coding I just know to record it...

Training / Books / Sites:

MrExcel's Products: Books, CDs, Podcasts Discuss topics related to Holy Macro! Products: Learn Excel from MrExcel, VBA and Macros for Microsoft Excel,Holy Macro! It's 2500 VBA Examples CD, Guerilla Data Analysis Using Microsoft Excel and Excel Knowledge Base CD and the MrExcel Podcasts.
http://www.mrexcel.com/forum/forumdisplay.php?f=19

How to Learn to Write Macros
http://articles.excelyogi.com/playin...ba/2008/10/27/

How to use the macro recorder
http://articles.excelyogi.com/

Click here and scroll down to Getting Started with VBA.
http://www.datapigtechnologies.com/ExcelMain.htm

If you are serious about learning VBA try
http://www.add-ins.com/vbhelp.htm

Excel Tutorials and Tips - VBA - macros - training
http://www.mrexcel.com/articles.shtml

See David McRitchie's site if you just started with VBA
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Debra Dalgleish has some notes how to implement macros here:
Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

Creating custom functions
http://office.microsoft.com/en-us/excel/HA011117011033.aspx

Writing Your First VBA Function in Excel
http://www.exceltip.com/st/Writing_Your_First_VBA_Function_in_Excel/631.html

http://www.excel-vba.com/
http://www.mvps.org/dmcritchie/excel/getstarted.htm
http://www.exceltip.com/excel_links.html

(livelessons video)
Excel VBA and Macros with MrExcel
ISBN: 0-7897-3938-0
http://www.amazon.com/Excel-Macros-M...7936479&sr=1-1

http://www.youtube.com/user/ExcelIsFun#g/search

http://www.xl-central.com/index.html

http://www.datapigtechnologies.com/ExcelMain.htm

Dependent validation lists. Debra has a neat little tutorial here.
http://www.contextures.com/xlDataVal02.html

Data Validation > Drop-Down Lists - Dependent
http://www.bettersolutions.com/excel/EGH188/QE229212022.htm

Cascading queries
http://www.tushar-mehta.com/excel/ne...ing_dropdowns/

http://www.contextures.com/xlDataVal05.html
http://www.contextures.com/xlDataVal08.html#Larger

Excel Data Validation - Add New Items
http://www.contextures.com/excel-data-validation-add.html

Programming The VBA Editor - Created by Chip Pearson at Pearson Software Consulting LLC
This page describes how to write code that modifies or reads other VBA code.
http://www.cpearson.com/Excel/vbe.aspx

Locating files containing VBA
Searching Files in Subfolders for VBA code string:
http://www.dailydoseofexcel.com/arch...a-code-string/

http://www.pcreview.co.uk/forums/thread-978054.php

Excel 2003 Power Programming with VBA, by John Walkenbach

VBA and Macros for Microsoft Excel, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel Hacks 100 Industrial-Strength Tips & Tools, by David & Traina Hawley

VBA and Macros for Microsoft Excel 2007, by Bill Jelen "Mr.Excel" and Tracy Syrstad

Excel 2007 Book: you can try this...there is a try before you buy ebook available at this link…
http://www.mrexcel.com/learnexcel2.shtml

DonkeyOte: My Recommended Reading:
Volatility
http://www.decisionmodels.com/calcsecretsi.htm

Sumproduct
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Introduction to Array Formulas
http://www.xtremevbtalk.com/showthread.php?t=296012

Using Pivot Tables and Pivot Charts in Microsoft Excel
http://peltiertech.com/Excel/Pivots/pivotstart.htm

A List of Pivot Table links at Jon Peltier's site (contributed by Debra Dalgleish) is here:
http://peltiertech.com/Excel/Pivots/pivotlinks.htm

Email from XL - VBA & Outlook VBA
http://www.rondebruin.nl/sendmail.htm
http://www.outlookcode.com/article.aspx?ID=40

Excel Function Dictionary
http://www.xlfdic.com/

Excel 2007 function name translations
http://www.piuha.fi/excel-function-name-translation/

Dynamic Named Ranges
http://www.contextures.com/xlNames01.html

Where to paste code in VBE VBA
Introducing the Excel VBA Editor
http://www.ask.com/web?qsrc=2417&o=101881&l=dis&q=Where+to+paste+code+in+the+Excel+VBA+Editor

VBA for Excel (Macros)
http://www.excel-vba.com/excel-vba-contents.htm

VBA Lesson 11: VBA Code General Tips and General Vocabulary
http://www.excel-vba.com/vba-code-2-1-tips.htm

Excel VBA -- Adding Code to a Workbook
http://www.contextures.com/xlvba01.html

Basics of array formulas
http://www.youtube.com/view_play_lis...7E7E9CA63304D3

Array formula data extract formulas
http://www.youtube.com/watch?v=Tp7I5u1MqiM
http://www.youtube.com/watch?v=R5ZWAiNJLNo
http://www.youtube.com/watch?v=132ZdpxBm1U

How to create Excel Dashboards
http://www.contextures.com/excel-dashboards.html
http://chandoo.org/wp/excel-dashboards/

Excel Dashboard / Scorecard Ebook
http://www.qimacros.com/excel-dashboard-scorecard.html

Unique Record Counting and Data Extract formulas
http://www.youtube.com/watch?v=uUrI8hoj8BA

And, as your skills increase, try answering posts on sites like:
http://www.mrexcel.com/
http://www.excelforum.com/
http://www.exceltip.com/exceltips.php
http://www.ozgrid.com/

 

[Anusuya12]

Thank you for the response Hiker.
Were you able to work on the excel to word macro?