Return cell values for non-matching & matching criteria

**jenz_skallemose** · 11-10-2012, 03:10 PM

Hey. I have a problem solving the following:

I have 2 sheets with different values. In row 1 in sheet 1 I have the values
AA AB & AC. Under these values i have other values. So under AA i have 2 and under AB i have 3 and under AC I have 4. In row 1 i sheet 2 i have values AA AB & AD. Under these values i also have values. So under AA i have 4, under AB i have 3 and under AD I have 2. I want to use vba to
write the following to a new sheet in the same workbook (sheet 3). The values AA AB, AC & AD in sheet 3 in row 1. and their corresponding values. This means AA in row 1 column 1 and in row 2 column 1 the value will be 2. In row 3 column 1 the value should be 4. In total this would look like:

Row 1: AA AB AC AD
Row 2: 2__ 3__ 4__ 0__
Row 3: 4__ 3__ 0__ 2__

This also means when there is not an entry the code needs to insert a "0".

How can i do this?

**mike7952** · 11-10-2012, 04:33 PM

It would be more helpful if you can upload a workbook with the way sheets 1 an 2 are formatted and show in sheet 3 your desired results.

**jenz_skallemose** · 11-10-2012, 05:37 PM

Issue.xls

Sorry here is the file.

**mike7952** · 11-10-2012, 06:58 PM

Will there always be only 3 rows?

**jenz_skallemose** · 11-11-2012, 04:09 AM

The data comes in different sizes which means the following:
The nummer of columns in Row 1 Will differ, meaning that sometimes there can be more points like "AN" and "AT" hence they need to be included in sheet3. These new entries Will also have corresponding values that needs to be inserted as well... But the number of rows in each sheet is always fixed...

**jindon** · 11-11-2012, 06:02 AM

jenz_skallemose

Test the attached and see if this works for you.

**jenz_skallemose** · 11-11-2012, 07:01 AM

Very Nice made. It seems to work perfect. How can I get/see the code?

**jindon** · 11-11-2012, 07:21 AM

Download the file again.

See the codes in

Module1 "test" sub procedure code.
Ark3 Sheet module "Sheet_Activate" event code calling "test".
ThisWorkbook module, Workbook_Open event code calling "test".

So it will always update whenever you activate "Ark3" sheet.

**jenz_skallemose** · 11-11-2012, 08:22 AM

Ok now I can see it. Thank you very much, I appreciate it a lot. I'm new to this forum so I'm not exactly sure how it works.

**jenz_skallemose** · 11-11-2012, 11:11 AM

Looking at your code, I have thought of two new questions:

First question:
Let's say that the data I need to return in the different sheets range from row 2 to 20. But instead of wanting all the data, I only need data from row 4 to 10 to be put into the new sheet ("Ark 3"). How would you modify your code then?

Second question:
Lets say that the data looked like the one i attached now. If you were doing the code with a for loop how could you modify this loop to provide the same answer. I was doing the following:

Please Login or Register  to view this content.

Thanks

**jindon** · 11-11-2012, 08:40 PM

First question:

What if the data has less than 10 rows?

Second question:

I don't think "For Next Loop" fits well for this particular problem.
I would rather use Range.Find or Worksheet.Function.Match method to see if the matching heading exists or not.

1) Clear Sheet3.
2) Copy Sheet1 data to Sheet3.
3) Loop through Row1(Heading) to find matching heading in Sheet2 Row1.
4) If matching heading found, copy from whichever row(s) of data you want to under the matching column.
5) If not, add new column in Sheet3.

This is what I would do.

**jenz_skallemose** · 11-12-2012, 04:05 PM

The data will always be a fixed number of rows which is 45, so that particular problem will not happen.
Ok, that's also what I was guessing, but I wasn't sure though. I see what you mean with your and I'll try to work on it.

**jenz_skallemose** · 11-12-2012, 04:07 PM

The data will always be a fixed number of rows which is 45, so that particular problem will not happen.
Ok, that's also what I was guessing, but I wasn't sure though. I see what you mean with your description and I'll try to work on it.

**jindon** · 11-12-2012, 04:21 PM

Originally Posted by jenz_skallemose

The data will always be a fixed number of rows which is 45, so that particular problem will not happen.

Please Login or Register  to view this content.

**jenz_skallemose** · 11-13-2012, 11:52 AM

Ah that was a clarifying moment. Now I think I understand the whole part of the code as well

Thank you so much for your help - it's very appreciated.

Return cell values for non-matching & matching criteria

LinkBack

Thread Tools

Rate This Thread

Display

Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Re: Return cell values for non-matching & matching criteria

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions