View original (French)

Excel : sorting issue with SIREN and SIRET

Elcaprio -  
 Elcaprio -
Hello,

I have a table of 5000 SIRET entries and 5000 SIREN entries. The SIREN are the same as the SIRET, I need to reconcile the two to delete the SIREN (I have data that is on the SIREN rows that need to appear on the corresponding SIRET rows; otherwise, I would simply delete the SIREN).
So I’m stuck because copying line by line 5000 SIREN to paste at the corresponding SIRET is tedious!!!
For example:
I have the SIRET 50040020030010 and the corresponding SIREN: 500400200, and the goal is to have the SIRET on one line and the SIREN on the line below.
Thank you for your responses. I'm quite stuck

Configuration: Windows XP / Internet Explorer 8.0

12 answers

Answer 1 / 12
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
Hello,

Where can I find your SIREN numbers (sheet name and column)? The same for the SIRET...
Name of an empty sheet in the workbook?
Aren't you allergic to macros?

Best regards,
Franck P
0
Elcaprio
 
The sheet is called: AdresseClient France and the column is O for both.
I don't understand anything about macros xD
0
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
Is the first line that contains a SIREN (or SIRET) line 1? 2? 5?
Are there any empty cells in this column O?
0
Elcaprio
 
There are empty cells and my SIRET numbers are before the SIREN numbers.
In fact, I have a client list with the SIRET numbers and from a file provided by the insurance, I need to input the insurance amount for each client, but the file identifies the clients by the SIREN numbers.
0
Answer 2 / 12
PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   834
 
Hello

First solution: add zeros behind the SIREN to have labels that can sort both lists together

the formula =TEXT($A$4,"00000")

set the cell to standard format, then group your lists and sort

second solution

extract the first 9 characters in a column with the formula =LEFT(B5,9)
and group your lists and look for duplicates, the formula to search for duplicates

=IF(COUNTIF($C$4:$C$9,C4)>1,1,0)

in my example, the SIREN is in column C
0
Answer 3 / 12
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
Well, as the request evolves, could you "whip up" an example file for us with names, SIREN, SIRET, and other fake data, just like it is currently in your file, but in the correct columns AND the expected results? You can send it to us using cjoint.com...
--
Best regards,
Franck P
0
Answer 4 / 12
Elcaprio
 
I'm sorry, I may not have been very clear in my request.
I have a client file from the company that gives me the amount of insurance granted and I need to update it with the information provided yesterday by the insurance.

This gives me this: http://cjoint.com/?BFCkpcP4v9I

For the insurance file, I only included the columns that I need; there is a lot of other information that I don't use.

Another point: once the update is done, I do not keep the insurance information.
0
Answer 5 / 12
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
In your test file, all the SIRET numbers form a "block," with no empty cells between each number. Is that the case with your file? Or are there empty cells here and there?
--
Best regards,
Franck P
0
Answer 6 / 12
Elcaprio
 
I have 3700 entries out of 4900 with a SIRET or SIREN; indeed, I have entries where only the SIREN is displayed.
Thank you for your responses and your patience.
0
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
Thank you for your responses and your patience There is no problem. The thing is that with a macro, you absolutely need to know the position of each element in your file.

So let me summarize:
in your sheet named: AddressClient France (without space between Address and Client, but with a space between Client and France)
you have a "database" from A1 to Sx composed, in the first 5000 rows of your client base with SIRET number (14 digits), then in the next 5000 rows your data with: in column A the same client references from A2 to A5000 and in column O the SIREN (9 digits)

You want to obtain, in the same sheet (which I will call result) the complete list of your data with two contiguous rows per client, one containing the SIRET and one containing the SIREN
for example:
A2: 411001 O2: 50010050040000
A3: 411001 A3: 500100500
with of course the preservation of all row data...


Is that correct???
0
Elcaprio
 
That's right, except for a small error: the client reference for the insurance is not the same.

The insurance file contains no header, so it's a bit of a pain to navigate.
As a result, I only retrieve the SIREN and the insurance amount.

My final goal is to have the insurance amount in column S for a given SIRET corresponding to the SIREN.

If the first 9 characters match, then copy "insurance amount" into column S.
0
Answer 7 / 12
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
So, last questions:
1- the first 9 digits of your SIRET are all different, unlike in your example file:
50010050040000
50010050050000
50010050060000
50010050070000
50010050080000
50010050090000
50010050100000
50010050110000
50010050120000
2- you want the numbers transmitted by the insurance, which are between S5001 (approximately) and S10000, to be placed between S2 and S5001 next to the correct SIRET numbers.

OK?
--
Best regards,
Franck P
0
Elcaprio
 
Yes, they are all different. I used the same ones for simplification in the example.
The only column that matters to me is the insurance amount column.
I was talking about placing the insurance line below the company line just to manually enter the insurance amount, but it's true that if it can also be automated, then let's go for it.
I put the SIRENs in the same column as the SIRETs because I thought I could solve my problem through sorting or filters. So if they end up in another column, that's not a problem.
Thinking further, I came up with a formula like:
IF(LEFT(U2,9)=LEFT(O2,9),V2,"")
In column U, there are the SIRENs and in V the amounts. But it doesn't work...
0
Answer 8 / 12
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
In fact, I dropped the unnecessary macro in this case.
I assume your SIRET numbers are in column O from O2 to O5001, your SIREN numbers, in number format, are in column O from O5005 to O10006, the amounts granted by the insurance are in column S from S5005 to S10006, and you want your results in column S from S2 to S5001.
In S2 enter the formula:
=IF(O2="","",VLOOKUP(VALUE(LEFT(O2,9)),$O$25:$S$34,5,FALSE))

Formula to be "dragged" using the fill method down to S5001...

--
Best regards,
Franck P
0
Answer 9 / 12
Elcaprio
 
I don't understand the $O$25:$S34$ in the formula.
I just copied the formula into S2 and I put the information where you told me to, but I have #N/A appearing.
0
pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 772
 
Normal, I messed up!:... My tests worked on this range. It needs to be replaced with $O$5001:$S$10006...
Ooops!
=IF(O2="";"",VLOOKUP(VALUE(LEFT(O2,9)),$O$5005:$S$10006,5,FALSE))
0
Answer 10 / 12
Elcaprio
 
Thank you for your responses.
Unfortunately, I can't apply your formulas :(
To avoid having to scroll down to line 5005 the next time my internship supervisor uses the file, I have placed the SIREN in column U and the amounts in V, so she will just have to copy/paste the columns and place them respectively in U2 and V2.
So I have this now: http://cjoint.com/?BFCp2mCGpdn
0
PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   834
 
Are you sure that the lines will match?

That's why he will use the VLOOKUP function.
0
Elcaprio
 
Indeed, the lines will not match.
That's why a formula needs to be created so that the amount is placed on the correct line.
0
Answer 11 / 12
PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   834
 
In T2, enter this formula: =CNUM(LEFT(O2,9))

and in U2, enter this one:

=IF(ISERROR(VLOOKUP(T2,$O$2:$S$34000,5,FALSE)),"",VLOOKUP(T2,$O$2:$S$34000,5,FALSE))
0
Answer 12 / 12
PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   834
 
Hello

Place this formula in S2

=IF(ISERROR(VLOOKUP(VALUE(T2),$O$25:$S$34,5,FALSE)),"",VLOOKUP(VALUE(T2),$O$25:$S$34,5,FALSE))

and this one in T2

=LEFT($O25,9)

and adjust the range O25:S34 to your range
Then drag it down the entire height
-3
Elcaprio
 
What do you mean by "adjust the field O25: S34 to your field"?
0
PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   834
 
You need to set the cell of the reference column and the first row of the search area Oxxx and the last cell of the last column Sxxx in the example.
0
Elcaprio
 
Thank you for your responses.
Unfortunately, it still doesn't work.
Here is the formula with the updated field:
=IF(ISERROR(VLOOKUP(VALUE(T2),$O$2:$S$3400,5,FALSE)),"",VLOOKUP(VALUE(T2),$O$2:$S$34000,5,FALSE))
0
Elcaprio
 
I just realized that I can't change the format. However, I had set a custom format: [>999999999]########_######;Standard
The problem may come from there.

By sorting using the "filter" tool, I see that in my SIRET column (so O), there are variable character strings: most are made up of 14 digits (normal for a SIRET), but I also have some that are 9 digits. I have many that start with 0, in my opinion that's not normal and it must not help.

I think I won't bother you any longer; I will enter it manually :-/
0
PHILOU10120 Posted messages 6463 Registration date   Status Contributor Last intervention   834
 
In column O, we only use the left 9 characters with the formula in column T T2, I take =left(O25,9) so in row 25 and in column O, I take the left 9 characters for the VLOOKUP.
0