LOOKUP and fill in data

[marshak]

[REVISED QUESTION, ALONG WITH ADDITIONAL SPREADSHEET POSTED BELOW]

Hi everyone!

I need some assistance with the following.
I am tasked to fill in the attached SAMPLE spreadsheet using the data found in the 2003 sheet.

Here's what I have to do:

Look for each Transaction ID under Column A of the Matrix tab. Look for those numbers in the 2003 tab. If found in the 2003 tab, fill in the data in the Matrix sheet ((Dates, Quantity Total, Cost). If Transaction ID is not found in the 2003 tab, place N/A.

I am not quite sure, but some transaction #s found in the Matrix tab may have multiple dates (or multiple quantities). Can this also be tabulated into the Matrix sheet (adding extra rows for these instances)?

Please let me know if you have any questions. I hope that I have not made this more complicated in my explanation.

Thanks so much for your help!

[BadlySpelledBuoy]

On the Matrix sheet, change the header in B1 to "Date_Applied" and get rid of the trailing space in C1 so it just reads "Quantity" rather than "Quantity ".
Then put the below formula in B2 then copy right and down as required:

Please Login or Register  to view this content.

There are no duplicate Transaction IDs in the sample data provided. You can easily check if your real data does have any by putting the below formula in P2 on the 2003 sheet and copying down:

Please Login or Register  to view this content.

If any of the values are greater than 1 then it's a duplicate.

If that is the case then attach an updated workbook showing a duplicate or two and manually create what you want it to look like after.

It may be that you need to use helper columns or even VBA to get the desired result.

BSB

[avk]

I suggest you go with pivot table.
Only one thing, add one "Amount" column in 2003 sheet (p column) with =sum(k2*o2)
Select complete data range > go to insert > click on table
create pivot table > in row labels transaction_id & part_no
in values : quantity, cost, amount.
See attach file.
When you add new data > go to pivot table > go to option tab > click on "Refresh All"
your new data auto update.

[Glenn Kennedy]

Please amend your sheet to show HOW you want duplicates to be displayed...

[marshak]

Thanks so much! I will try both solutions now, and let you know.

[marshak]

Okay, I have some clarification on what I need now. Thanks for being patient with me.

Here's what I have to do:

First thing....
I have an Excel sheet of about 500,000 rows of data (Data). I have another Excel sheet with roughly 200 rows of data (Matrix). I need to look up the PART# found in A2 of Matrix and see if it occurs in any of the 500,000 rows of column A of Data.

Second thing...
If that PART# found in column A of Matrix is found in column A of Data, I need to extract the data from Data (DATE_APPLIED, QUANTITY, Cost) and place them into the same named columns found in Matrix.

I have placed the following formula in cell B2, copied it across to cell D2, then down the rows...but it has not worked. Just gives me #N/A

=INDEX('Data'!B2:D449112,MATCH($A2,'Data'!A2:A449112,0),MATCH(B$1,'Data'!B1:D1,0))

What am I doing wrong? Am I using the best function to accomplish what I need to?

I am attaching a sample size of the spreadsheet (I could not attach the entire file...was too large)

This post is also posted under this forum:
https://www.mrexcel.com/forum/excel-...x-vlookup.html

[Glenn Kennedy]

In your sample... there are very few matches. However, use this, copied across and down:

=IFERROR(VLOOKUP($A2,Data!$A:$D,COLUMNS($A:B),FALSE),"")

[marshak]

Thanks so much for that formula. It is working great, with the exception of not catching a few matches. I have verified that my numbers are numbers (in both sheets). What can cause the formula not to pick up on a match?

[marshak]

I have figured out the formatting issue that I mentioned above. The formula provided finds all of the matches now. I need some further help...

Some of the Part #s found in the Matrix sheet have multiple matches in the Data sheet. Meaning, some Part #s have multiple Date_Applied entries. Is there a way to bring all of that data into the Matrix sheet.

For example, if a Part # in the Matrix sheet has three Date_Applied entries (along with three Quantity and Cost entries), can Excel add 2 additional rows under that Part # and place the data into those rows? I am attaching a picture which shows Part# 137645 with 4 Date_Applied entries and the additional rows and data.

Thanks again for your help.

[BadlySpelledBuoy]

can Excel add 2 additional rows under that Part # and place the data into those rows?

Yes it can, but you'd probably find this easier with VBA than with formulas alone.

Just out of interest, why not put =COUNTIF(Matrix!A:A,A2) in E2 on the Data sheet (of your attachment in post#6) and copy down to the end of the data.
You could then filter on column E to show anything greater than zero and the result will be what you're looking to create in the other sheet without any hassle.

You could just copy and paste it into the other sheet.

BSB

[avk]

Or you get result through pivot.
In data sheet "E" column =row()-1
Insert pivot table.
See attachment.

[marshak]

You guys ROCK!! I was able to get the data that I needed in a quarter of the time, and made my deadline! THANKS SO MUCH to all of you!!!