Please Help - Match and seperate information

Hi There

Please assist with the following

I have 3 sets of data sources:
PWC original asset file (See Column D)
DD source to match PWC (See Column D)
GV source to match PWC (See Column D)

I would like formulas to separate this information: This is just an example attached, I have more lines to separate

1. PWC & DD matched only against ERF (Column G)
2. PWC & DD unmatched against ERF (Column G)
3. PWC & GV matched only against ERF (Column G)
4. PWC & GV unmatched against ERF (Column G)
5. PWC & DD & GV matched (Column G)
6. PWC & DD & GV unmatched (Column G)

then a formula to seperate the duplicate PWC against ERF (Column G) then Township (Column J)

Hi

Thanks for your quick response

I have attached the file named BOOK4

Done. Have a look

Version 2

I haven't included PWC unmatched to both DD & GV

It is now included.

So

I need to separate the source (Column D) into different tabs

1. PWC & DD matched only against ERF (Column G)
2. PWC & DD unmatched against ERF (Column G)
3. PWC & GV matched only against ERF (Column G)
4. PWC & GV unmatched against ERF (Column G)
5. PWC & DD & GV matched (Column G)
6. PWC & DD & GV unmatched (Column G)

Then a tab for duplicate records under source (Column D) with PWC against ERF (Column G) then Township (Column J)

ok.

Under column D

there are 3 data sources (PWC, DD & GV)

PWC being the original
DD & GV are the sources to authenticate the give data.

Now I would like to separate under different categories
1. PWC vs DD Matched (Tab2)
2. PWC vs GV Matched (Tab3)
3. PWC vs DD unmatched (Tab 4 & 5)
4. PWC vs GV unmatched (Tab 6 & 7)
then those PWC unmatched, I need to run a duplicate Tab

I will attach an example now

Hi shukla.ankur281190

So I have attached a dataset

Apart from the "ORIGINAL DATASET"

I would like to separate the data into the different tabs

Would you be able to help or do you need more indepth explanation?

Please advise

Regards
Vivek

remember the PWC duplicates with only work under the given ERF number (Column G) and Township (Column J)
If same ERF and different Township, it wont be considered as duplicates

So in essence I would like to populate the original dataset into the different tabs

Anyone there. Please help. Very Urgent!!

It is not obvious to me what is meant to be matched.

In your sample OID 11696986 ("PWC MATCHED TO DD & GV") does not exist in "Original dataset"

Look at the source column
Options of source:
01_GV_ECDC
00_PWC_MARCH2016
02_DD_ECDC

PWC (e.g 00_PWC_MARCH2016) being the original dataset
GV (e.g 01_GV_ECDC) or DD (02_DD_ECDC) may match or match to the original PWC with the given ERF under Column G

So basically we are matching PWC (00_PWC_MARCH2016) with GV or DD, or GV & DD, or without any match

It is not obvious to me what is meant to be matched.

In your sample OID 11696986 ("PWC MATCHED TO DD & GV") does not exist in "Original dataset"

...This example was extracted from the original dataset

Let me attache another spreadsheet with all the original datasets includes

Columns to focus on

Ringed in red

Match or unmatch PWC source with DD or GV in Column D, considering the ERF numbers on Column G

Attached is an example of a matched PWC to DD and GV

Guys. I need your help here please

VBA solution:

Please Login or Register  to view this content.

I haven't added logic for duplcates as I was not clear on what is required.

RUN button on "Original Dataset" column M

HI JohnTopley

You are BRILLIANT!!!

Thank you so much

I will figure out the duplicates. You just made my work a whole lot simpler.

You're welcome. Thank you for the feedback and rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Originally Posted by JohnTopley

You're welcome. Thank you for the feedback and rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

Do you perhaps have a formula for duplicate check on source column based on the ERF Numbers and township. Some what it can hilight or stand out to identify.

Also. If I add additional data or rows to your spreadsheet under the ORIGINAL DATASET, will the data pull thru on the various tabs?

Yes, it counts the rows in "Original Data" and adjusts if rows are added/deleted.

I will look at duplicate check and get back to you.

Looking at the data there is no obvious check for Duplication and looking at your manual results all the ERF values are different.

Hi John

Please assist with another spreadsheet

Same concept

Deeds data tab is the original,

I want to separate and match them to the relevant tabs

So a quick one

Match source column D using the erf column G and seperate them to the the different tabs like the last assignment

please

very urgent

thank you

Regards
Vivek

So another example of what i require:

Under deeds data Tab:

For every green colored cell under the source column we trying to match with non fill but the unique number on column G (ERF), pwc masterfile must match DD or none

PLease help

Please Login or Register  to view this content.

See attached: I have not thoroughly checked the results.

File too large (even zipped).

Hi John

Not sure how to run this,

It states subscript range error

Mail me [email protected]
Or drop box

Thank you

Try

Alt+F11 (opens VB Editor)

then "Insert" ==> "Module"

Copy/paste code into module

Back to Excel

Click "Developer" ==>"Macros"==>Select "Transfer_to_tabs"

I am now going out for a least an hour and a half.

subscript out of range...

ok managed to fix the subscript error :-)

it now works

u a star

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

ok, just one last request on this topic.

I need a count:

Example in "Deeds matched to Masterfile" tab -
For every sucessful match "pwc masterfile" matches to "DD_ECDC" =
"DD_BANTU" =

I've done a pivot table, but its counting wrong and not the results im looking for

do you want the count of "pcw masterfile" where there at least one of each category for a given "pcw masterfile"?

e.g ERF 832 has 10 BANTU and 2 ECDC si this count of 1?

how many ''pwc masterfile'' matched to
DD_BANTU
DD_ECDC
DD_CISKIE

So, this is just relating to "Deeds matched to Masterfile" tab

Thank you

Is this what you want?

In a "helper" column (one for each formula)

=IF(ISNUMBER(SEARCH("pwc",$D2)),IF(COUNTIFS($G$2:$G$6000,$G2,$D$2:$D$6000,"*ECDC*"),1,0),"")

=IF(ISNUMBER(SEARCH("pwc",$D2)),IF(COUNTIFS($G$2:$G$6000,$G2,$D$2:$D$6000,"*BANTU*"),1,0),"")

=IF(ISNUMBER(SEARCH("pwc",$D2)),IF(COUNTIFS($G$2:$G$6000,$G2,$D$2:$D$6000,"*CISKEI*"),1,0),"")

NOTE: there are some results where the "pwc masterfile" is duplicated for a given ERF

If the formulae are correct, simply sum the columns.