VBA: VLOOKUP with LOOP

[BrunoVeiga]

Good morning,


This is my first time here, I hope you guys are able to help me out. I recently started to work with VBA, I am finding it amazing, but really complex for a non-programming mindset person.
I have been mostly recording my VBAs and adjusting and improving as it goes, and it is a great way to learn. But I came across this topic that I cannot really find a way to implement. I have been watching videos and reading forums, but nothing exactly what I need. I am hoping someone is able to assist me here.


I would like to build a macro to automatize the below:


Combine information of 3 worksheets in one worksheet (Cognos).
I want to see in "Cognos" sheet only rows with NW status (NW status is shown at at Sheet VT11 in Colum J )


In the same macro I am also interested to run other details:
1. At Cognos sheet, delete rows of files that are not in the VT11 sheet and is not NW status in the Column J. until here it was fine, but... not further.
>The Cognos sheet should show only rows that are NW from the Column J at VT11 sheet.


2. At Cognos Sheet, Column C, =VLOOKUP(B2,'VT11'!A:B,2,FALSE) Loop until last Cognos sheet B2 row.
> Bring the Order # from VT11 sheet to Cognos sheet.


3. At Cognos Sheet, Column AK, implement =VLOOKUP(A2,TietanMasterExtra!A:BD,56,FALSE) Loop until last Cognos sheet A2 row.
> Bring the Additional Text info from TietanMasterExtra(BD) sheet to Cognos Sheet (AK)


4. At Cognos Sheet, Column AL, Loop until last row the =VLOOKUP(B2,'VT11'!A:O,14,FALSE)
> Bring the Name info from VT11sheet (N) sheet to Cognos Sheet (AL)


Thanks in advance for your help
Cheers
Bruno

 

[RCBricker]

I have a few questions to clarify some stuff before I can start to try and help.

1. At Cognos sheet, delete rows of files that are not in the VT11 sheet and is not NW status in the Column J. until here it was fine, but... not further.
>The Cognos sheet should show only rows that are NW from the Column J at VT11 sheet.

would it not be easier to clear COGNOS completely and filter VT11 by column J looking for only rows that show "NW"?
Are we bringing everything from VT11 sheet to COGNOS or only specific data?
What is the name of the field in VT11 that represents column J?

2. At Cognos Sheet, Column C, =VLOOKUP(B2,'VT11'!A:B,2,FALSE) Loop until last Cognos sheet B2 row.
> Bring the Order # from VT11 sheet to Cognos sheet.

What is the name of the B column in COGNOS?
What is the name of the field you are pulling data from in this VLOOKUP?

3. At Cognos Sheet, Column AK, implement =VLOOKUP(A2,TietanMasterExtra!A:BD,56,FALSE) Loop until last Cognos sheet A2 row.
> Bring the Additional Text info from TietanMasterExtra(BD) sheet to Cognos Sheet (AK)

What is the name of the A column in COGNOS?
What is the name of the AK column in COGNOS?
What is the name of the field you are pulling data from in this VLOOKUP?

4. At Cognos Sheet, Column AL, Loop until last row the =VLOOKUP(B2,'VT11'!A:O,14,FALSE)
> Bring the Name info from VT11sheet (N) sheet to Cognos Sheet (AL)

What is the name of the AL column in COGNOS?
What is the name of the field you are pulling data from in this VLOOKUP?

Once we get those answers I can begin to put something together.

oops one last question

Do you want the actual formulas in the cells or just pull the information?

 

[BrunoVeiga]

Hi RCBricker,
Thanks for your reply, It is the very 1st time I am doing it, so sorry if I am not being clear enough.
Here below the answer for your queries.

Originally Posted by BrunoVeiga
1. At Cognos sheet, delete rows of files that are not in the VT11 sheet and is not NW status in the Column J. until here it was fine, but... not further.
>The Cognos sheet should show only rows that are NW from the Column J at VT11 sheet.



would it not be easier to clear COGNOS completely and filter VT11 by column J looking for only rows that show "NW"? I need all the data/columns in the cognos Sheet.
Are we bringing everything from VT11 sheet to COGNOS or only specific data? from VT11 sheet, I will be coping to cognos sheet the columns A,B, N. Colum J will be used only as filtering reference.
What is the name of the field in VT11 that represents column J?Tender Status

Originally Posted by BrunoVeiga
2. At Cognos Sheet, Column C, =VLOOKUP(B2,'VT11'!A:B,2,FALSE) Loop until last Cognos sheet B2 row.
> Bring the Order # from VT11 sheet to Cognos sheet.



What is the name of the B column in COGNOS? Shipment Number



What is the name of the field you are pulling data from in this VLOOKUP? Column name: Reference document

Originally Posted by BrunoVeiga
3. At Cognos Sheet, Column AK, implement =VLOOKUP(A2,TietanMasterExtra!A:BD,56,FALSE) Loop until last Cognos sheet A2 row.
> Bring the Additional Text info from TietanMasterExtra(BD) sheet to Cognos Sheet (AK)


What is the name of the A column in COGNOS? Column name: Reference number
What is the name of the AK column in COGNOS? Column name: "Supplemental Text (Tietan 208-Additional text info)"
What is the name of the field you are pulling data from in this VLOOKUP? Column name: Additional Text Info

Originally Posted by BrunoVeiga
4. At Cognos Sheet, Column AL, Loop until last row the =VLOOKUP(B2,'VT11'!A:O,14,FALSE)
> Bring the Name info from VT11sheet (N) sheet to Cognos Sheet (AL)


What is the name of the AL column in COGNOS? Column name :Name
What is the name of the field you are pulling data from in this VLOOKUP? Column name: Name

Once we get those answers I can begin to put something together.

oops one last question

Do you want the actual formulas in the cells or just pull the information? The way I see it, with the macro, the formulas are going to be inserted in the Cognos sheet, columns C, AK and AL pulling data from TietanMasterExtra sheet and VT11 sheet.

here you can vizualize how the excel looks like:
https://drive.google.com/file/d/0B9V03Nw0RCcGSFNpMmJDZGRmTW8/view?usp=sharing

Thanks

 

[RCBricker]

I need all the data/columns in the cognos Sheet.
Are we bringing everything from VT11 sheet to COGNOS or only specific data? from VT11 sheet, I will be coping to cognos sheet the columns A,B, N. Colum J will be used only as filtering reference.
What is the name of the field in VT11 that represents column J?Tender Status

This is confusing. At first you say you need everything from V11 and then you say you only need A,B & N.

here you can vizualize how the excel looks like:
https://drive.google.com/file/d/0B9V03Nw0RCcGSFNpMmJDZGRmTW8/view?usp=sharing

Thanks

I cant view files on the internet while at work.

 

[RCBricker]

ok I will take a stab at what I think you want.

COGNOS sheet:
has specific headers that are needed to be kept.
Should only contain rows that contain the text "NW" on the VT11 sheet
Will contain 3 formulas that look information up from the other two sheets

QUESTIONs****
the VT11 sheet will depend which rows of data will be found on the COGNOS sheet...Other than the COGNOS headers, will there be any other data? If there is a previous data on the COGNOS sheet will the code need to check the data to see if it matches the VT11 sheets (for "NW"). Can we not just delete all data on the COGNOS sheet (except) headers, since the COGNOS should only have data from VT11 that has "NW"?

VT11 sheet:
This sheet is the primary data sheet where two out of the three VLOOKUP formulas will pull information.
This sheet is used first by the code to determine via filtering for "NW" what data should be found on the COGNOS

TietanMasterExtra:
This sheet is only used to pull data for one of the VLOOKUP formulas

 

[BrunoVeiga]

Hi RCBricker

This is confusing. At first you say you need everything from V11 and then you say you only need A,B & N. I did not express myself correctly than. Indeed we could start by clearing VT11 sheet to only show NW value at column J.

I cant view files on the internet while at work.

I will go to FAQ now to try find out how to attach a file or an image to facilitate our communication.

Cheers
Bruno

 

[RCBricker]

Hi RCBricker

I will go to FAQ now to try find out how to attach a file or an image to facilitate our communication.

Cheers
Bruno

no need I can make a dummy sheet.

no need to clear sheet VT11. since we are going to filter the data anyway. My question is COGNOS. Before we filter VT11, what data is found on the COGNOS sheet?

I need a couple more header names

What is the field header for column C of COGNOS?
What is the field header for column A of VT11?
What is the field header for column N of VT11? what column does it get moved to in COGNOS?
What is the field header for column A of TietanMasterExtra?

 

[RCBricker]

I will be coping to cognos sheet the columns A,B, N.

2. At Cognos Sheet, Column C, =VLOOKUP(B2,'VT11'!A:B,2,FALSE).
3. At Cognos Sheet, Column AK, implement =VLOOKUP(A2,TietanMasterExtra!A:BD,56,FALSE) .
4. At Cognos Sheet, Column AL, Loop until last row the =VLOOKUP(B2,'VT11'!A:O,14,FALSE)

in the above description:

Column B is brought in from the VT11 sheet with the formula in Column C of Cognos
Column N is brought in from the VT11 sheet with the formula in Column AL of Cognos

How is column A being brought from VT11 to Cognos?

 

[BrunoVeiga]

QUESTIONs****
the VT11 sheet will depend which rows of data will be found on the COGNOS sheet (yes, cognos sheet will have more rows than VT11 sheet, however, I only want keep those lines that corresponds to the NW in the VT11 sheet)...Other than the COGNOS headers, will there be any other data? (Yes, cognos will be many rows with data, however some columns are going to be empty) If there is a previous data on the COGNOS sheet will the code need to check the data to see if it matches the VT11 sheets (for "NW").(Yes) Can we not just delete all data on the COGNOS sheet (except) headers, since the COGNOS should only have data from VT11 that has "NW"? (I am interested to keep the data from Cognos that are matching with the NW from VT11. If Cognos column B matches with VT11 column A and It is NW at column J, I would like to keep Cognos line, if not can be deleted. )

VT11 sheet:
This sheet is the primary data sheet where two out of the three VLOOKUP formulas will pull information. (Yes, This is where everything should start, this is our reference. I am only interested to see in Cognos the rows that are NW in the VT11 sheet)
This sheet is used first by the code to determine via filtering for "NW" what data should be found on the COGNOS (Yes.)

TietanMasterExtra:
This sheet is only used to pull data for one of the VLOOKUP formulas (Correct.)


Sheet: Cognos
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]AK[/TD]
[TD="align: center"]AL[/TD]
[/TR]
[TR]
[TD="align: center"]Reference Number[/TD]
[TD="align: center"]Shipment Number[/TD]
[TD="align: center"]Order[/TD]
[TD="align: center"]Delivery order number[/TD]
[TD="align: center"]Status[/TD]
[TD="align: center"]Supplemental text[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD]will contain rows with data[/TD]
[TD]will contain rows with data[/TD]
[TD]Data coming from VT11 columB
=VLOOKUP(B2,'VT11'!A:B,2,FALSE)[/TD]
[TD]will contain rows with data[/TD]
[TD]to be only "NW"[/TD]
[TD]Data coming from TietanMasterExtra columBD
=VLOOKUP(A2,TietanMasterExtra!A:BD,56,FALSE)[/TD]
[TD]Data coming from VT11 columN
=VLOOKUP(B2,'VT11'!A:N,14,FALSE)[/TD]
[/TR]
</tbody>[/TABLE]

Sheet: TietanMasterExtra
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]BD[/TD]
[/TR]
[TR]
[TD="align: center"]Reference number[/TD]
[TD="align: center"]Order number[/TD]
[TD="align: center"]MasterShipment number[/TD]
[TD="align: center"]Additional Text Info[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Sheet: VT11
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]J[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]Shipment Number[/TD]
[TD="align: center"]Document reference[/TD]
[TD="align: center"]Tender status[/TD]
[TD="align: center"]Name[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Cheers
Bruno

 

[RCBricker]

Last question (I hope).

where does the Reference number data for the Cognos sheet come from?

and did you see the question from post #8?

How is column A from VT11 moved to cognos? Just a copy paste into column B of Cognos?