PDA

View Full Version : VBA: Cross-match first two sheets and save result sheet as excel workbook.



tyavan
01-02-2019, 11:01 AM
Crossmatch first ID in ColumnB2/sheetA with all IDs in ColumnB/SheetB and find match/mismatch in a new next sheet "RESULT" and save as MS excel workbook with the name of the first ID in ColumnB2/sheetA.
PS. IDs can also have no data at all in rows in either sheet.
PS. Data can be upto any column.

tyavan
01-03-2019, 09:41 AM
:help

大灰狼1976
01-03-2019, 06:27 PM
Hi tyavan!
If there are contents behind x-name2, x-name3,etc... what should we do?

tyavan
01-03-2019, 07:51 PM
For you I understood my mistake. I am totally wrong in writing my requirement.



In SheetA: The first column is ID. And each id has data in respective cells rows. Here mock data in the attached sheet of ID "x-name1" is from 2B to 2L in cell rows.

Similarly in SheetB: The first column is ID. And each id has data in respective row cells.



Now, Crossmatch first ID data (i.e in the row starting from B2) with all ID data in sheetB one by one.

The expected result is given in the RESULT sheet with defined headlines.

And find match/mismatch in a new next sheet "RESULT" and save as MS excel workbook with the name of the first ID in ColumnA2/sheetA.
PS. IDs can also have no data at all in rows in either sheet.
PS. Data can be upto any column.

大灰狼1976
01-03-2019, 08:28 PM
Sorry i still don't quite catch your meaning.
for example, there are contents behind SheetA's x-name1 and x-name2,
and SheetB content unchanged, then the result:
x-name1 1a_6
x-name1 1a_7
x-name1 1a_8
x-name1 1a_9
x-name2 1a_6
x-name2 1a_7
x-name2 1a_8
x-name2 1a_9
Is this understanding correct?

tyavan
01-03-2019, 09:25 PM
They are just ID and this is how VBA will crossmatch ID data.
In Result sheet column C to F need to full filed as per match and mismatch.

For example, I want to crossmatch x-name1 with 1a_6 as below. Based on this data I want to find matching and mismatching data with their place of occurrence and fill the RESULT sheet.



ID-B
1
2
3
4
5
6
7
8
9
10
11


x-name1
I
am
going
to
London
from
there
we
will
visit
Japan []


1a_6
I
am
going
to
London
from
there
we
will
visit
Japan []



The crossmatch result x-name1 with 1a_6 "in RESULT sheet is row no 2 data".

tyavan
01-04-2019, 12:46 PM
The crossmatch result x-name1 with 1a_6 " resulted in RESULT sheet row 2 data". This is just one crossmatch. Similarly, x-name1 need to be cross-matched with other IDs i.e, 1a_7 to 1a_9 and we will get the result in next row one by one. Hope this is clear now. I also need to select the range of rows and columns as there are blank cells occurring randomly.

tyavan
01-05-2019, 10:15 AM
I need the result as it is in excel sheet. Is it possible?