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
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,440
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.
 

mozga628

New Member
Joined
Feb 18, 2019
Messages
4
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.
 

mrshl9898

Well-known Member
Joined
Feb 6, 2012
Messages
1,440
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.
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,432
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top