Monday, 26 September 2016

Troubleshoot:Database creation of Raw Data which has one name appearing multiple times in sheet 1 but sheet 2 it should only appear once

Database creation of Raw Data which has one name appearing multiple times in sheet 1 but sheet 2 it should only appear once

Excel (Sheet 1 contains name of cities where one city has multiple entries; I do not want to use remove duplicate function; instead in sheet 2 I want to write a formula in cell A1 where the name of the city appears only once then i can formulate the other
cells as per the data required.
Note the name of the city in sheet 2 must not appear twice)

Keys to the Problem Database creation of Raw Data which has one name appearing multiple times in sheet 1 but sheet 2 it should only appear once

Download Error Fixer (Free)

Say the raw data names are in column A of Sheet1 from A1 thru A100.
 
First install the following User Defined Function in a standard module:
 
Public Function ListUniques(r As Range)
Dim N As Long, rr As Range, I As Long
Dim coll As Collection
Set coll = New Collection
N = r.Rows.Count
ReDim Temp(1 To N, 1 To 1)
For I = 1 To N
    Temp(I, 1) = ""
Next
On Error Resume Next
For Each rr In r
    coll.Add rr.Value, CStr(rr.Value)
Next
For I = 1 To coll.Count
    Temp(I, 1) = coll.Item(I)
Next
ListUniques = Temp
End Function
 
Then hi-light cells A1 thru A100 in Sheet2 and enter the following array formula:
=ListUniques(Sheet1!A1:A100)
 
NOTE:  The formula must be entered with CNTRL-SHFT-ENTER rather than just the ENTER key.
 
 

User Defined Functions (UDFs) are very easy to install and use:
1.
ALT-F11  brings up the VBE window
2.
ALT-I
    ALT-M opens a fresh module
3.
paste the stuff in and close the VBE window
If you save the workbook, the UDF will be saved with it.
To remove the UDF:
   1.
bring up the VBE window as above
   2.
clear the code out
   3.
close the VBE window
To use the UDF from Excel:
=myfunction(A1)
To learn more about macros in general, see:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
or
http://www.cpearson.com/excel/WritingFunctionsInVBA.aspx
for specifics on UDFs
 
 
 
 
If you do not feel comfortable with macros, see:
 
http://www.xldynamic.com/source/xld.xlFAQ0021.html

Using Windows troubleshooter:

  • Click on Search on Charm bar and type Troubleshooting. Click on the very first option.
  • A new window will open. Click View all from the left pane.
  • Scroll down to the bottom and click on Windows update.
  • Troubleshooter window for Windows update will pop up. Click on NEXT. It may ask you for administrative permission.
  • The troubleshooter will automatically detect problems and fix it.
  • Now, close this window and try download/install the app which was previously showing error.

Another Safe way to Repair the Problem: Database creation of Raw Data which has one name appearing multiple times in sheet 1 but sheet 2 it should only appear once:

How to Fix Database creation of Raw Data which has one name appearing multiple times in sheet 1 but sheet 2 it should only appear once with SmartPCFixer?

1. Download Error Fixer. Install it on your system.

2. After the scan is done, you can see the errors and problems which need to be repaired.

3. The Repair part is done, the speed of your computer will be much higher than before and the errors have been removed. You can also use other functions in SmartPCFixer. Like dll downloading, windows updating and print spooler error repair.


Related: How to Update & Download NVidia GeForce 6100/nForce 420 WHQL Certified driver v.178.13,How to Update & Download NVidia GeForce 7600 GS Video Driver v.295.75 Certified,How Can I Update & Download NVidia GeForce 820M Driver v.344.48 WHQL,[Solved] Download NVidia GeForce GTX 560M VGA Driver v.296.17 Certified,Way to Update & Herunterladen NVidia GeForce GT 130M Video Treiber v.295.75 Certified,How to Update & Download SONY SVE14A1X1RH Realtek Ethernet Driver,Where to Download SONY SVS13A2W9ES Bluetooth Driver (Intel) v.2.6 - 2.6.23.40059,Best Way to Update & Download SONY VGN-CR203E Conexant HDAUDIO SoftV92 Data Fax Modem with SmartCP Setup Program v.7.62.0.50 driver,Best Way to Download SONY VGN-FE790G/N Wireless LAN Driver v.10.6.0.29,Method to Update & Download SONY VGN-NR31Z/S Firmware Extension Parser Device v.8.0.2.3,How to Fix Error 0x0000c1f5 SoluciĆ³n?,Error 0x80071a91 Win7 Fix Patch,Error 0x800ccc60 Windows Live Mail [Solved],Error Message 0x800ccc79 Outlook 2000 Fix,What is Error 0xc1ab0001?,[Answered] x64 saplugin dll,How to Fix Problem - Kb943729 Download?,Troubleshooting: Bccode D1 Error,Java Runtime Tech Support,Troubleshooting: ERROR NO SUCH GROUP Error
Read More: Date\/Time manipulation using Excel 2003 [Solved],How Can You Fix - Defrag and consolidate system files in Windows 7 SP1?,How to Fix Error - Default Gateway not available?,How to Fix Problem - DEP shuts down IE 8 when playing freeslots.com?,Fast Solution to Problem: Custom Built PC Won't Boot

No comments:

Post a Comment