PDA

View Full Version : HELP!!! Fastest code in consolidating columns with different headers



sbbmaster09
06-06-2016, 05:13 AM
Hi,

I am looking for a fastest code that will do the flow below. I have 2 workbooks wtih "maindata" and "sourcedata" sheet. The sourcedata sheet has 100+ columns and thousands of data. The maindata sheet is where all the needed data from the sourcedata sheet should be copied. maindata new sheet has already have the headers (Row 1).

The tool flows like this:
1] get the value in sheet maindata B1
2] find the value in sheet sourcedata columns 1-100
3] when the value is found or exactly the same headers, copy the entire column from sheet sourcedata to sheet maindata of the same header
4] when the value is not found, go to next value in sheet maindata C2
5] and repeat step 2 or 4 until all headers in sheet maindata are populated


Additional functions needed are:
1. Each files to be opened should have count of rows copied
2. For each file copied, column 1 should be filled up by filename
3. repeat the whole process for all the files opened



I am not yet expert to use arrays and for loops so I will be needing a help to code this.

Attach are the files. Consolidation Automation is the maindata and the 2.csv is the sourcedata.

Thank you.

snb
06-06-2016, 05:46 AM
Hi,

I am looking for a fastest code ......

Me too

Paul_Hossler
06-06-2016, 06:27 AM
2.CSV is a SYLK file, not a true csv

SYLK files have some extra things in them so 2.xlsx has some potential garbage data also

sbbmaster09
06-06-2016, 06:37 AM
But still I am able to open them, I just having problems on the coding side of using for loops or do while on activating the file and copying the data needed back to the template

p45cal
06-06-2016, 06:38 AM
cross posted
http://www.mrexcel.com/forum/excel-questions/945446-help-fastest-code-consolidating-columns-different-headers.html
http://www.excelguru.ca/forums/showthread.php?6177-HELP!!!-Fastest-code-in-consolidating-columns-with-different-headers

sbbmaster09
06-06-2016, 06:43 AM
Thanks, I will look unto this links.. and will get back if this would help me out. Thank you for the references.

SamT
06-06-2016, 08:05 AM
Refer to : snb's more suggestions .. (http://www.snb-vba.eu/index_en.html)

Transpose Main Row 1 to an array

Transpose Sourcedata to an array. This puts the headers in position arrSrc(j, 1)

For max speed maindata headers are in the same order as which they appear in sourcedata rows

Then

j = LBound(arrSrc)
For i = LBound(arrMain) + 1 to UBound(arrMain) + 1
Do while j <= Ubound(arrSrc)
If arrSrc(j, 1) = arrMain(i) then
'set appropriate range on maindata to Transpose(arrSrc(j))
j = j + 1
Exit Do
Else
j = J =1
Loop
Next i

If they are not in the same order then the inner Do While must be a simple For loop.

SamT
06-06-2016, 08:06 AM
What is multiposting? (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3)