I need an Excel formula \ stacker news ~devs

pull down to refresh

351 sats \ 29 comments \ @DarthCoin 16 Dec devs

I need a quick simple formula for excel data. I want to copy certain data from each "service" line into a specific "service" column (will be in another file, but for convenience I put in this example in the same sheet).

So I need a formula with VLOOKUP or IF or both, I don't know exactly how, but to do the following:

search for the ID number in column A
search for the "service name" in column B
copy the data (column C) into each service column accordingly.

ID	Service	Data
1234	AAA	4032		AAA	BBB	CCC	DDD	EEE
1234	BBB	145	1234	vlookup or IF ?	vlookup or IF ?	vlookup or IF ?	vlookup or IF ?	vlookup or IF ?
1234	CCC	136,5
1234	DDD	48
1234	EEE	56

3,000 sats paid

DarthCoin's bounties

view all past bounties

25 sats \ 1 reply \ @1fatmess 16 Dec freebie

VLOOKUP is so old school.

Use =INDEX(MATCH(…)…)

0 sats \ 0 replies \ @DarthCoin OP 16 Dec

I don't care which is better, I just need to copy / convert that data from lines to columns, with verifying each ID and service.

3131 sats \ 15 replies \ @SimpleStacker 16 Dec

Does this solve your issue?

=IFERROR(VLOOKUP($F2,FILTER($A$2:$C$11,G$1=$B$2:$B$11),3,FALSE),"")

Replace $F2 with the column that contains ID in the wide table

Replace $A$2:$C$11 with the full range of your original table

Replace G$1 with the column that contains the service code column heading in the wide table

Replace $B$2:$B$11 with the original service code column

(Note, this will only work properly if each ID, Service pair in the original data appears at most once.)

(Note 2, another suggestion would be to consider using a pivot table for your use case)

0 sats \ 0 replies \ @DarthCoin OP 16 Dec

idk why is returning this error and it prompts to "FILTER".

I am not at my desk, I am using somebody's else PC. gonna try on my other PC with another excel, libre office.

0 sats \ 12 replies \ @DarthCoin OP 16 Dec

This look good but still it give me error. Let me try replacing the separator , with ;

0 sats \ 11 replies \ @SimpleStacker 16 Dec

let me know if it works

0 sats \ 10 replies \ @DarthCoin OP 16 Dec

idk why it gives me error and it prompt to that FILTER. Must be something with locales settings. The formula looks really good, make sense and I see in your screenshot that copied the correct data as I want it.

I will give you the bounty anyways and figure it out starting from this formula. Thanks!

21 sats \ 9 replies \ @SimpleStacker 16 Dec

I wonder what version of Excel you are using. Mine was:

Microsoft® Excel® for Microsoft 365 MSO (Version 2411 Build 16.0.18227.20082) 64-bit

0 sats \ 7 replies \ @DarthCoin OP 16 Dec

unbelivable ! On a libre office excel, same file opened and paste the formula it doesn't give error, but it display nothing. wtf is wrong with this ?

tried also on a google sheets with the same file and it gives error "circular dependency"

21 sats \ 6 replies \ @SimpleStacker 16 Dec

In that specific picture, the formula is pointing to $F2 but your ID column is on E so it should be $E2. Also, the G$1=... part should say F. Essentially, your second table is one column shifted to the left from my example.

I also tried on google sheet and it should work as long as you fix that error.

30 sats \ 0 replies \ @DarthCoin OP 16 Dec

Yeah I saw it, still the same. Yes, google sheets works !

reply on another page

0 sats \ 4 replies \ @DarthCoin OP 16 Dec

TIL the excel formula that contain FILTER is available only for Office 365 MSO. The thing is mi wife have to gather some huge data (like 10k records) from 2 different excels. If I upload those to google sheet, I cannot construct the formula from 2 different files. One file is also getting some data from another file... is a mess.

In the end I made a temporary copy of the data sheet into destination file and working from google sheets.

view replies

0 sats \ 0 replies \ @DarthCoin OP 16 Dec

Yeah must be something related. I tried on another PC with office 2019. Gonna try on mine with libre office.

0 sats \ 0 replies \ @DarthCoin OP 16 Dec

deleted by author

21 sats \ 1 reply \ @SimpleStacker 16 Dec

(deleted) I didn't see the requirement to look up ID as well. Will return with another post.

0 sats \ 0 replies \ @DarthCoin OP 16 Dec

Sorry is incomplete. I think I forgot to add something. The ID column is filled with many numbers so the formula also have to find the one corresponding to D3 number ID in the column A (I put the same number to see where should come the data). So some other VLOOKUP should be added.

ID	Service	Data
1234	AAA	4032		AAA	BBB	CCC	DDD	EEE
1234	BBB	145	1234	vlookup or IF ?	vlookup or IF ?	vlookup or IF ?	vlookup or IF ?	vlookup or IF ?
1234	CCC	136,5	456
1234	DDD	48	658
1234	EEE	56
456
456
658
658

10 sats \ 5 replies \ @sigit 16 Dec

If you can modify the sheet with your data, I would recommend creating a new column which concatenates ID and SERVICE (but with a delimiter to prevent false lookups)

Then you can use this to generate a simple VLOOKUP, eg:

For those who aren't familiar - the use of the $ sign means that the formula will "stick" to that column (or row) when dragging a formula along or down.

0 sats \ 4 replies \ @DarthCoin OP 16 Dec

The thing is that ID and service are repeated lines too. So ID 1234 could be on 4 or 6 lines, but having different services. This literally switching lines to columns with lookup for 2 references and return the data for each service.

I know I have to make a vlookup + if formula but can't get it working right now.

10 sats \ 3 replies \ @sigit 16 Dec

The formula works for the case you describe - see below (also I fixed the range element, is now $A:$D)

Or are you saying there can be multiple lines for the same combination of ID and Service?

In which case you would like to sum the Data column?

0 sats \ 2 replies \ @DarthCoin OP 16 Dec

In which case you would like to sum the Data column?

No I don't want to sum data. Only want to copy data from each service line into a service column.

ID	Service	Data
1234	AAA	4032		AAA	BBB	CCC	DDD	EEE
1234	BBB	145	1234	vlookup or IF ?	vlookup or IF ?	vlookup or IF ?	vlookup or IF ?	vlookup or IF ?
1234	CCC	136,5	456
1234	DDD	48	658
1234	EEE	56
456
456
658
658

0 sats \ 1 reply \ @sigit 16 Dec

Does this make sense?

LKP	ID	Service	Data
=B2&"\|"&C2	1234	AAA	4032		AAA	BBB	CCC	DDD	EEE
^^ Drag this DOWN	1234	BBB	145	1234	=VLOOKUP($E3&"\|"&F$2,$A:$D,4,0)
	1234	CCC	136,5	456	^^ Drag this down, then drag them all across
	1234	DDD	48	658
	1234	EEE	56
	456
	456
	658
	658

By "Drag" I mean the fact that you can click on the bottom right of the cell and "drag" the formula down. This will update the cell references accordingly and display the corresponding values.

Here is a copy of the spreadsheet: https://paste.nostrdev.com/?d557826675410582#BBFDzTe8txYBzY9xhHNfiNvRNtPVZr4kvVACPCrnNfdN

0 sats \ 0 replies \ @DarthCoin OP 16 Dec

I get formula error. Why did you create column A, just to concatenate column B and C? I don't need exactly that, I need only to search and compare.

0 sats \ 2 replies \ @cyphercosmo 16 Dec

I was able to do it in Google Sheets with the following:

=QUERY(
   A$1:C$6, 
   "SELECT C 
    WHERE A = " & D3 & " 
      AND B = '" & E2 & "'", 
   0
)

In this case the first argument A$1:C$6 could be a range in the same sheet, a different tab, or a different sheet.

The second argument has some concatenation, D3 and E2 which are the service names you listed horizontally plus the ID, so you'll need to reference them in the query and these variables will change for each new value horizontally. So the second will reference D3 and F2 instead.

And then last argument is just to inform the query that the data range has a header and it shouldn't be considered in the query.

0 sats \ 1 reply \ @DarthCoin OP 16 Dec

but this is only a query not a copy, right?

0 sats \ 0 replies \ @cyphercosmo 16 Dec

That is correct, the formula/function resolves those values but it doesn't copy, so as long as the reference remains healthy it should be dynamic.