SQL Import

mozga628

New Member
Joined
Feb 18, 2019
Messages
4
i have created an excel table with imported data from a SQL server.
the data being imported in a single column is the users Domain\UID.
Columns B, C, D, etc are used to MANUALLY enter application user ID's (mainframe, etc).

today a new hire was added to the SQL table and was successfully inserted into column A in my excel spreadsheet...
the problem is that all the rows with application ID's did not shift down to accommodate the inserted new user record.
now everything below the new hire is off by 1 row.

how can i import updates with inserted new users AND insert blank cells on the same row as the new domain\uid?

BEFORE
Adam userid1 userid2 userid3
Beth userid4 userid5 userid6
David userid7 userid8 userid9
Eddie userid10 userid11 userid12

AFTER
Adam userid1 userid2 userid3
Beth userid4 userid5 userid6
Cathy userid7 userid8 userid9
David userid10 userid11 userid12
Eddie

SHOULD BE
Adam userid1 userid2 userid3
Beth userid4 userid5 userid6
Cathy
David userid7 userid8 userid9
Eddie userid10 userid11 userid12

if this has anything to do with the SQL, the command am using is:
Select AD_ID From "DATABASENAME" order by AD_ID
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
So SQL returns column A, while BC and D are static data in the spreadsheet?

There's no reason for them to shift with the new data.

You'll need to create a table with all the names and other data and keep adding to it as a new name appears.


I'd grab the before, copy and paste on a new tab "Data", then on the SQL tab use VLOOKUPs to return the information. When you come across N/As as the name list grows, then add that name and IDs to the bottom of the Data tab.
 
Upvote 0
obviously i was hoping for an automated solution, but your idea is a good one, and well within my excel skillset.
i have already built tabs for reporting purposes using index /match refencing this data, and to build another table is going to make this spreadsheet excessively bulky very quickly. i appreciate your response and if no automated solution presents itself, its seems like the path i will take.
 
Upvote 0
Unless the other data is found in your database somewhere I don't see any option here.

There's always PowerQuery if you're worried about size. You'd still need to manually update the table, but you could have that plus the SQL in the background linked by ID, then just insert the combined table into your spreadsheet.
 
Upvote 0
An automated solution could use Worksheet_Change to trigger when the table cells have changed and move rows in the adjacent columns. You would need to store the current column A values in an array in order to determine which row has changed.
 
Upvote 0

Forum statistics

Threads
1,223,893
Messages
6,175,244
Members
452,622
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top